## 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;