CPS 430/542 Lecture notes: Subqueries

---

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.