Division relational operation

[SWEngineer]

Of the DB operations I came to is the Division operation. I really tried to understand this operation from different sources but didn't get the idea of this operation.

Can someone thankfully describe it or at least direct me to a source that explains this operation clearly?

Thanks.

[tesuji]

Hi SWEngineer

Consider these two tables:

table student:
Name
-------
Ada
Benita 		
Cecile
Fatma

table subjects_passed:
Name     subject
---------------------------
Ada      Relational Algebra
Fatma    Relational Algebra
Ada      Databanks
Fatma    Databanks

select name as "No exam passed" from students division select name from subjects_passed;

Result:

No exam passed
--------------
Benita 		
Cecile

So divison (except) operator gives all students which have not passed any exam.

That is, the result set contains all rows which are in left table but not in right table of division operator.

Same result is given by left join:

[B]select students.name as "No exam passed" from students  left join subjects_passed on students.name = subjects_passed.name where subject is null;[/B]

Another name for this relational operation is EXCEPT what is the official standard SQL/1999 name. There are also other names: difference, minus (Oracle).

Further relational operations are union and intercept (can be computed by division, so not necessary).

-- tesu

[SWEngineer]

Thanks tesuji. I'm asking about the division operator, and in the resources it is something different than what you showed here. What do you think?

[tesuji]

Ah sorry, I misread you.

Obviously you meant plain division operator such as 13/3 makes 4 (if you were to stumble about archaic transact sql).

Simply forget my previous posting.

-- tesu

[SWEngineer]

Ah sorry, I misread you.

Obviously you meant plain division operator such as 13/3 makes 4 (if you were to stumble about archaic transact sql).

Simply forget my previous posting.

-- tesu

It is not actually the division you mentioned. Division is one of the extraction operations (i.e; Union, difference, join, select, ...etc).

This is what I mean.

[tesuji]

It is not actually the division you mentioned. Division is one of the extraction operations (i.e; Union, difference, join, select, ...etc).

This is what I mean.

Gracious SWEngineer!

DIVISION is synonym for

1. difference --> read your posting !

2. minus (oracle)

3. EXCEPT what is its official name by sql 1999 standard

So you may go back and try to understand my FIRST posting !

-- tesu

[SWEngineer]

Gracious SWEngineer!

DIVISION is synonym for

1. difference --> read your posting !

2. minus (oracle)

3. EXCEPT what is its official name by sql 1999 standard

So you may go back and try to understand my FIRST posting !

-- tesu

As the manga guide to databases it states that there are two groups of data extraction operations:

1- Set operations:
* Union
* Difference
* Intersection
* Cartesian product
2- Relational operations:
* Projection
* Selection
* Join
* Division

So, division and difference are different.

[Swede1875]

Yes, Division is of course an entirely different opperation than difference. It is most often used when you want to find tuples in on table that relate to all tuples in another table.

For instance:
We have three tables: Boyz with data on severel boys, like name adress et cetera, and boyzID as PK. Girlz with data on a lot of girls with girlzID as PK and Love that connects boyz with girlz by and having boyzID and girlzID as a compound pk.

if we would like to write a query that answers which boys who have loved all girls, we could use a division.
(something like Boyz ÷ Girlz if we would write it in relational algebra.)

The idea behind a division is to conduct a double negation: We will searh for any boy who has not loved a certain girl and then not show those boys in the result.

To achieve this with SQL, we need to identify three parts: 1) What do we want to show? (Here data from the table Boyz ) 2) what constitutes all? (Here data from the table Girlz) and 3) how are 1 & 2 joined (Here data from the table Love)

It would look like this (just replace the boys and girls parts with the tables (or joined tables) that you need to use. Each of the tree parts can be found after the three select statements below

SELECT name
FROM Boyz
WHERE NOT EXISTS (SELECT *
FROM Girlz
WHERE Girlz.girlzID NOT IN (SELECT Love.girlzID
FROM Love
WHERE Boyz.boyzID = Love.boyzID))