•Why "Bud" appears twice? 47
49 Formal Semantics • Almost the same as for single-relation queries: 1. Start with the product of all the relations in the FROM clause. 2. Apply the selection condition from the WHERE clause. 3. Project onto the list of attributes and expressions in the SELECT clause.
50 Operational Semantics • Imagine one tuple-variable for each relation in the FROM clause. – These tuple-variables visit each combination of tuples, one from each relation. • If the tuple-variables are pointing to tuples that satisfy the WHERE clause, send these tuples to the SELECT clause.
51 Example drinker bar drinker beer tv1 tv2 Jennifer Bud Jennifer Joe's Likes Frequents to output check these are equal check for Joe • Find beers liked by drinkers who frequent Joe's bar
52 Explicit Tuple-Variables • Sometimes, a query needs to use two copies of the same relation. • Distinguish copies by following the relation name by the name of a tuple-variable, in the FROM clause. • It's always an option to rename relations this way, even when not essential.
53 Example • From Beers(name, manf), find all pairs of beers by the same manufacturer. – Do not produce pairs like (Bud, Bud). – Produce pairs in alphabetic order, e.g. (Bud, Miller), not (Miller, Bud). SELECT b1.name, b2.name FROM Beers b1, Beers b2 WHERE b1.manf = b2.manf AND b1.name < b2.name;
56 Subquery in the from clause • A parenthesized SELECT-FROM-WHERE statement ( subquery ) can be used in FROM clause • Example: – select * from (select * from Beers) as b – Note tuple variable needed to name the relation generated by the subquery
Subquery in the where clause • Introduced by '=' (or '!=') – x = (subquery) – x can be an attribute or a tuple of attributes – Subquery needs to return exactly one result • Introduced by 'in' (or 'not in') – x in (subquery) – Subquery may return multiple results 57
Subquery introduced by '=' • Subquery needs to return exactly one result! 58 Return > 1 tuple select * from Beers where (name, manf) = (select name, manf from Beers where name = 'Bud'); select * from Beers where (name, manf) = (select name, manf from Beers where manf = 'Anheuser-Busch');
Subquery introduced by 'in' • Subquery may return multiple results 59 select * from Beers where (name, manf) in (select name, manf from Beers where manf = 'Anheuser-Busch');
60 Example • From Beers(name, manf) and Likes(drinker, beer), find the name and manufacturer of each beer that Steve or Bill likes. The set of beers Steve or Bill likes SELECT name, manf FROM Beers WHERE name IN ( SELECT beer FROM Likes WHERE drinker = 'Steve' or drinker = 'Bill' );
Without subquery • Does this query produce the same result? 62 SELECT name, manf FROM Beers b, Likes l WHERE b.name = l.beer and l.drinker = 'Steve' or l.drinker = 'Bill';
Correct equivalent subquery • Note the "distinct" and grouping (using parentheses) of two conditions on drinker 63 SELECT distinct name, manf FROM Beers b, Likes l WHERE b.name = l.beer and (l.drinker = 'Steve' or l.drinker = 'Bill');
Introduced by comparison operators • <comparison operator> <any/all> (subquery) – Comparison operators: =, !=, <, >, <=, >=, <> • Examples – x >= all (subquery) – x <= all (subquery)
You've reached the end of your free preview.
Want to read all 676 pages?
- Fall '14