SQL Subqueries

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;