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?
58
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
59
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)
–
x = any (subquery) // equivalent to "x in
(subquery)"
–
x != all (subquery) // equivalent to "x not in
(subquery)"
•
Is "x != any (subquery)" equivalent to "x not in
(subquery)"?
60

61
Example
•
From Sells(bar, beer, price), find the beer(s)
sold for the highest price.
•
What about beers with "the lowest price"?
price from the outer
Sells must not be
less than any price.
SELECT beer
FROM Sells
WHERE price
>= ALL
(
SELECT price
FROM Sells);

Introduced by "exists" or ”in"
•
Exists
–
exists (subquery): evaluated to true if subquery
has at least one result
•
IN
–
X in (subquery): evaluated to true if element X is
in table resulting from subquery.
•
NOT EXISTS and NOT IN also possible
62

63
Example Query with EXISTS
•
Where does this query do?
select name
from Beers b1
where not exists (
select name
from Beers b2
where b2.name <> b1.name and b2.manf = b1.manf);

Often Interchangable…
64
-- IN
SELECT
*
FROM Employee
WHERE DeptName IN (
SELECT DeptName
FROM Dept
)
-- EXISTS
SELECT *
FROM Employee
WHERE EXISTS (
SELECT 1
FROM Dept WHERE
Employee.DeptName = Dept.DeptName
)

IN vs. Exists
While there is no general rule as to whether you
should prefer IN or EXISTS, but:
•
IN predicates tend to be more readable than
EXISTS predicates
•
EXISTS predicates tend to be more expressive
than IN predicates (i.e. it is easier to express very
complex SEMI JOIN)
•
There is no formal difference in performance.
There may, however, be a
huge performance
difference on some databases
.
65

Agenda
•
SQL DML (Data Manipulation Language)
–
SQL query
–
Relations as bags
–
Joins
–
Grouping and aggregation
–
Database modification
•
SQL DDL (Data Definition Language)
–
Define schema
66

67
Bag Semantics for SFW Queries
•
The SELECT-FROM-WHERE statement uses
bag
semantics
–
Selection: preserve the number of occurrences
–
Projection: preserve the number of occurrences
(no duplicate elimination)
–
Cartesian product, join: no duplicate elimination

Set Operations on Bags
•
Union: {a,b,b,c} U {a,b,b,b,e,f,f} =
{a,a,b,b,b,b,b,c,e,f,f}
–
add
the number of occurrences
•
Difference: {a,b,b,b,c,c} – {b,c,c,c,d} = {a,b,b}
–
subtract the number of occurrences
•
Intersection: {a,b,b,b,c,c}
{b,b,c,c,c,c,d} = {b,b,c,c}
–
minimum of the two numbers of occurrences
68
!

How to implement them?

#### You've reached the end of your free preview.

Want to read all 149 pages?

- Spring '18
- From Sells