Outline Multirelation SQL queries CS 235: Subqueries ANY, ALL, - PDF document

Outline • Multirelation SQL queries CS 235: • Subqueries – ANY, ALL, EXISTS, IN Introduction to Databases • Aggregation Svetlozar Nestorov Lecture Notes #10 Multirelation Queries Formal Semantics • List of relations in FROM clause. • Same as for single relation, but start with the • Relation-dot-attribute disambiguates attributes product of all the relations mentioned in the from several relations. FROM clause: • Example: Find the beers that the frequenters of – Apply selection (for bags) – WHERE clause Spoon like. • Likes(drinker, beer) Frequents(drinker, bar) – Apply projection (extended) – SELECT clause SELECT beer FROM Frequents, Likes WHERE bar = ‘Spoon' AND Frequents.drinker = Likes.drinker; Operational Semantics Explicit Tuple Variables • Sometimes we need to refer to two or more • Consider a tuple variable for each relation in the copies of a relation. FROM. • Use tuple variables as aliases of the relations. • Imagine these tuple variables each pointing to a • Example: Find pairs of beers by the same manufacturer. tuple of their relation, in all combinations ( e.g ., nested loops). SELECT b1.name, b2.name • If the current assignment of tuple-variables to FROM Beers b1, Beers b2 tuples makes the WHERE true, then output the WHERE b1.manf = b2.manf AND attributes of the SELECT. b1.name < b2.name; 1

Explicit Tuple Variables Examples • SQL permits AS between relation and its • Find all bars that sell two different beers at tuple variable the same price. • Note that b1.name < b2.name is needed to • Find all bars that sell three different beers avoid producing (Bud, Bud) and to avoid at the same price. producing a pair in both orders. • Find all drinkers that frequent a bar that serves their favorite beer. Subqueries Example • Find bars that serve Miller at the same price • Result of a select-from-where query Spoon charges for Bud can be used in the where-clause of SELECT bar another query. FROM Sells WHERE beer = 'Miller' AND price = • Simplest case: subquery returns a (SELECT price single, unary tuple (like a constant). FROM Sells WHERE bar = ‘Spoon’ AND beer = ‘Bud’); • Scoping rule: an attribute refers to the most closely nested relation with that attribute. • Parentheses around subquery are essential. The IN Operator The EXISTS operator • Tuple IN relation is true iff the tuple is in the • EXISTS(relation) is true iff the relation is relation. nonempty. • Find the beers that are the unique beer by their • Find the name and manufacturer of beers that manufacturer: Leo likes SELECT name Beers(name, manf) and Likes(drinker, beer). FROM Beers b1 SELECT * WHERE NOT EXISTS FROM Beers (SELECT * WHERE name IN FROM Beers (SELECT beer WHERE manf = b1.manf AND FROM Likes name <> b1.name); WHERE drinker = ‘Leo’); 2

Correlated Subquery Quantifiers • Scoping rule: to refer to outer Beers in the inner • ANY and ALL behave as existential and subquery, we need to give the outer a tuple universal quantifiers, respectively. variable, b1 in this example. • Find the beer(s) sold for the highest price, given • A subquery that refers to values from a Sells(bar, beer, price) surrounding query is called a correlated SELECT beer subquery . FROM Sells • A correlated subquery must be evaluated (by the WHERE price >= ALL system) for every tuple in the outer query. (SELECT price FROM Sells); Example Union, Intersection, Difference • Find the beer(s) not sold for the lowest price, • (subquery) UNION (subquery) produces the given Sells(bar, beer, price). union of the two relations. • Similarly for INTERSECT, EXCEPT = intersection and set difference. – Not supported by MySQL but you can write an equivalent query. Example Forcing Set/Bag Semantics • Find the drinkers and beers such that the • Default for select-from-where is bag; default for drinker likes the beer and frequents a bar union is set. that serves it. – Why? Saves time of not comparing tuples as we generate them. (SELECT * FROM Likes) • Force set semantics with DISTINCT after INTERSECT SELECT. (SELECT drinker, beer – But make sure the extra time is worth it. FROM Sells, Frequents • Force bag semantics with ALL after UNION. WHERE Frequents.bar = Sells.bar ); 3

Example Aggregations • Find the different prices charged for beers. • Sum, avg, min, max, and count apply to attributes/columns. SELECT DISTINCT price FROM Sells; • Count(*) applies to tuples. • Find all beers liked by Leo or Jim. • Use these in lists following SELECT. • Find the average price of Bud. SELECT AVG(price) FROM Sells WHERE beer = 'Bud'; • Counts each tuple (for each bar that sells Bud) once. Eliminating Duplicates Before Aggregation • Find the number of different prices at which Bud is sold. SELECT COUNT(DISTINCT price) FROM Sells WHERE beer = 'Bud'; • DISTINCT may be used in any aggregation, but typically only makes sense with COUNT. 4