Subqueries
Parenthesized SFW-query which can be used in the FROM and WHERE clauses:
SFW returns relations (can return a scalar too)
Relations can be used in other queries.
Example: Find the salespersons who have the same commission as ‘Goro’
SELECT comm FROM Salesperson WHERE salpers_name = 'Goro'
Wrap this in parenthesis and use it.
SELECT salper_name FROM Salesperson WHERE comm = (SELECT comm FROM Salesperson WHERE salpers_name = 'Goro');
We could also say:
SELECT salper_name FROM Salesperson S, (SELECT comm FROM Salesperson WHERE salpers_name = 'Goro') C WHERE S.comm = C.comm;
In this case, the subquery must be some variable assignment C.
Find customers (names) who live in Chile and have bought Nigerian products using a subquery to first find the Nigerian products.
SELECT C.cust_name
FROM Customer C, Sale S, (SELECT prod_id
FROM Product, Manufacturer where Manufacturer.country='Nigeria'
and Product.manufactr_id = Manufacturer.manufactr_id) N
WHERE C.cust_id = S.cust_id and N.prod_id = S.prod_id and C.country='Chile';
In operator
<tuple> IN <relation> is true iff $t\in R$
Also NOT IN works too.
In this case <relation> is almost always a subquery. Example:
Find sales made from the Chicago office
SELECT *
FROM Sale
WHERE salpers_id IN (
SELECT salpers_ID
FROM Salesperson
WHERE office = 'Chicago');
What does this return?
| sale_id | date | cust_id | salpers_id | prod_id | qty |
|---|---|---|---|---|---|
| 01 | 02/28 | 100 | 10 | 2241 | 200 |
| 06 | 02/05 | 105 | 10 | 2241 | 100 |
You can also check a tuple:
…where (salpers_id, salpers_name) in (select salpers_id, salpers_name from…);
How is this different from join? (same in this example, could be different in others, how?)
Answer – The join may return matching results more than once
Example:
a1, b1 b1 c1
a2, b1 b1 c2
Would be drastically different.
Exists
EXISTS (<relation>)
True iff <relation> is not empty. Also NOT EXISTS (<relation>)
Example Salespersons who have sold something (anything):
SELECT salpers_name FROM Salesperson S1 WHERE EXISTS ( SELECT * FROM sale S WHERE S.salpers_id = S1.salpers_id );
***Exists operator is usually bounded from some outer relation (S1 in this case).
**As in relational calculus**
Any Operator
X = ANY (<relation>)
Where ‘=’ can be any kind of mathematical comparison.
Example: x >= ANY(R) means that x is bigger than or equal to any of the of tuples in $$R$$
ALL Operator
X = All (<relation>)
Where ‘=’ can be any kind of mathematical comparison.
Example: x >= ANY(R) means that x is bigger than or equal to all of the tuples in R
(Same as ANY for < > semantics)
Find the product description sold for the highest price. SELECT prod_desc
FROM Product
WHERE price >= ALL(
SELECT price
FROM Product);
Set operations
UNION, INTERSECTION, EXCEPT
R UNION S
R INTERSECT S
R EXCEPT S (set difference)
***Set operators use set semantics (not bag semantics), if you want bag semantics append ALL
R UNION ALL S
Find salespersons in Chicago that sold at least one table lamp.
(SELECT salpers_id
FROM Salesperson
WHERE office=’Chicago’)
INTERSECT
(SELECT salpers_id
FROM Sale, Product
WHERE Sale.prod_id = Product.prod_id
and Product.prod_desc = ‘Table Lamp’);
This doesn’t work in MySQL. Why?
We already have an intersect operator… called IN
SELECT salpers_id
FROM Salesperson
WHERE office=’Chicago’ and salpers_ID IN
(SELECT salpers_id
FROM Sale, Product
WHERE Sale.prod_id = Product.prod_id
and Product.prod_desc = ‘Table Lamp’);
Controlling duplicates
SELECT does not automatically eliminate duplicates like Projection in Relational Algebra.
SELECT DISTINCT … does
Example:
SELECT DISTINCT qty
FROM Sale;