上海交通大学 数据库原理 陆朝俊 课件09SQL1概要1.ppt

Lu Chaojun, SJTU * Scalar-valued Subquery If SELECT-FROM-WHERE produces a single value, then it can be used as a constant. May be extended to a single tuple: (v1,…,vn) Example = (SELECT … FROM) (SELECT … FROM) Lu Chaojun, SJTU Lu Chaojun, SJTU * Example SELECT name FROM S WHERE age = (SELECT age FROM S WHERE sno = ‘S1’); Scoping rule: an attribute refers to the most closely nested relation with that attribute. Parentheses around subquery are essential. Lu Chaojun, SJTU Lu Chaojun, SJTU * Subqueries that Produce Relations t IN R, t NOT IN R, NOT (t IN R) Check if tuple t is in relation R EXISTS(R), NOT EXISTS(R) Check if R is empty t ? ALL R, t ? ANY R Quantifiers ?, ? Lu Chaojun, SJTU Lu Chaojun, SJTU * Example SELECT name FROM S WHERE sno IN (SELECT sno FROM SC WHERE cno = ‘C2’); Subquery is computed only once. Lu Chaojun, SJTU Lu Chaojun, SJTU * Example SELECT name FROM S WHERE EXISTS (SELECT * FROM SC WHERE sno = S.sno AND cno = ‘C2’); Subquery is computed for each tuple in S. Correlated subquery Lu Chaojun, SJTU Lu Chaojun, SJTU * Example SELECT name FROM S S1 WHERE NOT EXISTS (SELECT * FROM S WHERE sno S1.sno AND age = S1.age); Note scoping rule. Lu Chaojun, SJTU Lu Chaojun, SJTU * Example SELECT name FROM S S1 WHERE age ALL(SELECT age FROM S WHERE snoS1.sno) Lu Chaojun, SJTU Lu Chaojun, SJTU * Conditions Involving Tuples List of scalar values (123,’foo’) (name,age,dept) (name,20,’CS’) Usage t IN R t ANY R components must match Lu Chaojun, SJTU Lu Chaojun, SJTU * Subqueries in FROM Clause Form SELECT ... FROM (SELECT ... FROM ... ) tuplevar WHERE ... Example SELECT name FROM S, (SELECT sno FROM C, SC WHERE C.cno=SC.cno AND C.name=‘DB’) Stud WHERE S.sno = Stud.sno; Lu Chaojun, SJTU Lu Chaojun, SJTU * Join Expressions SQL provides a number of expression forms that act like varieties of JOIN in re