CPS 430/542 Lecture notes: Subqueries

Coverage: [FCDB] §6.3 (pp. 264-276)

Subqueries (vs. Joins)

• we already seen them: (...) ∪ (...)
• other ways
• in FROM
• constant in WHERE
• relation in WHERE
• stored relation vs. parenthesized subquery
• correlated subquery: subquery which must be evaluated more than once
• (NOT) EXISTS, IN, ALL, ANY

Crisper joins

CROSS JOIN = Cartesian product

Movie CROSS JOIN StarsIn;

JOIN ... ON = |><|_C (theta-join), but then we need to do the SELECT manually

NATURAL JOIN = |><|

MovieStar NATURAL JOIN MovieExec;

Movie JOIN StarsIn ON title = movieTitle AND year = movieYear;

SQL join expressions (or syntactic sugar)

• Cartesian product
```SELECT *
FROM R, S;
```
=
```R CROSS JOIN S;
```
• natural join
```R(a,b)
S(b,c)

SELECT a R.b c
FROM R, S
WHERE R.b = S.b;
```
=
```R NATURAL JOIN S;
```
• theta-join
```R(a,b)
S(c,d)

SELECT
FROM R, S
WHERE b = c;
```
=
```R JOIN S ON b = c;
```
• outerjoin:
```R(a,b)
S(c,d)
```
• natural outerjoin:
• full natural outerjoin: R NATURAL FULL OUTER JOIN S; (equivalent to R FULL OUTER JOIN S ON R.b = S.b;)
• left natural outerjoin: R NATURAL LEFT OUTER JOIN S; (equivalent to R LEFT OUTER JOIN S ON R.b = S.b;)
• right natural outerjoin: R NATURAL RIGHT OUTER JOIN S; (equivalent to R RIGHT OUTER JOIN S ON R.b = S.b;)

• theta-outerjoin
• full theta-outerjoin: R FULL OUTER JOIN S ON b = c;
• left theta-outerjoin: R LEFT OUTER JOIN S ON b = c;
• right theta-outerjoin: R RIGHT OUTER JOIN S ON b = c;

Outerjoin syntax

```NATURAL [FULL | LEFT | RIGHT] OUTERJOIN
[FULL | LEFT | RIGHT] OUTERJOIN ON ...
```

References

 [FCDB] J.D. Ullman and J. Widom. A First Course in Database Systems. Prentice Hall, Upper Saddle River, NJ, Second edition, 2002.