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.

Return Home