Database

SQL Subqueries Management

Peter Wood

SQL queries
SQL Subqueries
Aggregation Queries

I one SQL query can be used in the evaluation of

another
I a query that is part of another is called a subquery
I subqueries can be used
I at the “top” level of an SQL query (union, intersection
and difference)
I in the WHERE clause
I in the FROM clause
 Database
Union of two tables Management

Peter Wood
Find all locations where a drinker lives or a pub is located:
SQL queries
SQL Subqueries
Aggregation Queries
 Database
Union of two tables Management

Peter Wood
Find all locations where a drinker lives or a pub is located:
SQL queries
SQL Subqueries
name loc name loc Aggregation Queries

Horse and Hound B Alice I

Hound and Hare I Bob B
Pubs: Drinkers:
March Hare B Carol I
Black Horse I Dave B
White Horse B Eve S
 Database
Union of two tables Management

Peter Wood
Find all locations where a drinker lives or a pub is located:
SQL queries
SQL Subqueries
name loc name loc Aggregation Queries

Horse and Hound B Alice I

Hound and Hare I Bob B
Pubs: Drinkers:
March Hare B Carol I
Black Horse I Dave B
White Horse B Eve S

(SELECT loc FROM Drinkers)

UNION
(SELECT loc FROM Pubs);
 Database
Union of two tables Management

Peter Wood
Find all locations where a drinker lives or a pub is located:
SQL queries
SQL Subqueries
name loc name loc Aggregation Queries

Horse and Hound B Alice I

Hound and Hare I Bob B
Pubs: Drinkers:
March Hare B Carol I
Black Horse I Dave B
White Horse B Eve S

(SELECT loc FROM Drinkers)

UNION
(SELECT loc FROM Pubs);

loc
Bloomsbury
Islington
Stratford
 Database
Intersection of two tables Management

Peter Wood

SQL queries
SQL Subqueries
Find locations where both a drinker lives and a pub is Aggregation Queries

located:
 Database
Intersection of two tables Management

Peter Wood

SQL queries
SQL Subqueries
Find locations where both a drinker lives and a pub is Aggregation Queries

located:

(SELECT loc FROM Drinkers)

INTERSECT
(SELECT loc FROM Pubs);
 Database
Intersection of two tables Management

Peter Wood

SQL queries
SQL Subqueries
Find locations where both a drinker lives and a pub is Aggregation Queries

located:

(SELECT loc FROM Drinkers)

INTERSECT
(SELECT loc FROM Pubs);

loc
Bloomsbury
Islington
 Database
Intersection of two tables Management

Peter Wood

SQL queries
SQL Subqueries
Find locations where both a drinker lives and a pub is Aggregation Queries

located:

(SELECT loc FROM Drinkers)

INTERSECT
(SELECT loc FROM Pubs);

loc
Bloomsbury
Islington

Note that UNION, INTERSECT and EXCEPT do remove

duplicate answers.
 Database
Difference of two tables Management

Peter Wood

SQL queries
SQL Subqueries
Aggregation Queries

Find locations where a drinker lives but no pub is located:

 Database
Difference of two tables Management

Peter Wood

SQL queries
SQL Subqueries
Aggregation Queries

Find locations where a drinker lives but no pub is located:

(SELECT loc FROM Drinkers)

EXCEPT
(SELECT loc FROM Pubs);
 Database
Difference of two tables Management

Peter Wood

SQL queries
SQL Subqueries
Aggregation Queries

Find locations where a drinker lives but no pub is located:

(SELECT loc FROM Drinkers)

EXCEPT
(SELECT loc FROM Pubs);

loc
Stratford
 Database
Difference of two tables Management

Peter Wood

SQL queries
SQL Subqueries
Aggregation Queries

Find locations where a drinker lives but no pub is located:

(SELECT loc FROM Drinkers)

EXCEPT
(SELECT loc FROM Pubs);

loc
Stratford

Note that the subqueries have to be defined over the

same set of attributes - loc in this case.
 Database
Union with renaming Management

Peter Wood

SQL queries
SQL Subqueries
Aggregation Queries

Find the names of all pubs in the database:

 Database
Union with renaming Management

Peter Wood

SQL queries
SQL Subqueries
Aggregation Queries

Find the names of all pubs in the database:

(SELECT name AS pub FROM Pubs)

UNION
(SELECT pub FROM Sells)
UNION
(SELECT pub FROM Visits);
 Database
Conditions involving relations Management

Peter Wood
There are a number of SQL operators that apply to a
relation (or expression) R and return a Boolean result: SQL queries
SQL Subqueries
Aggregation Queries
 Database
Conditions involving relations Management

Peter Wood
There are a number of SQL operators that apply to a
relation (or expression) R and return a Boolean result: SQL queries
SQL Subqueries
Aggregation Queries
I EXISTS R is true if and only if R is not empty
 Database
Conditions involving relations Management

Peter Wood
There are a number of SQL operators that apply to a
relation (or expression) R and return a Boolean result: SQL queries
SQL Subqueries
Aggregation Queries
I EXISTS R is true if and only if R is not empty
I s IN R is true if and only if s is equal to one of the
values in R (where, e.g., s is an attribute and R is a
unary (one-column) relation/expression)
 Database
Conditions involving relations Management

Peter Wood
There are a number of SQL operators that apply to a
relation (or expression) R and return a Boolean result: SQL queries
SQL Subqueries
Aggregation Queries
I EXISTS R is true if and only if R is not empty
I s IN R is true if and only if s is equal to one of the
values in R (where, e.g., s is an attribute and R is a
unary (one-column) relation/expression)
I s > ALL R is true if and only if s is greater than every
value in R
 Database
Conditions involving relations Management

Peter Wood
There are a number of SQL operators that apply to a
relation (or expression) R and return a Boolean result: SQL queries
SQL Subqueries
Aggregation Queries
I EXISTS R is true if and only if R is not empty
I s IN R is true if and only if s is equal to one of the
values in R (where, e.g., s is an attribute and R is a
unary (one-column) relation/expression)
I s > ALL R is true if and only if s is greater than every
value in R
I s > ANY R is true if and only if s is greater than at
least one value in R
 Database
Conditions involving relations Management

Peter Wood
There are a number of SQL operators that apply to a
relation (or expression) R and return a Boolean result: SQL queries
SQL Subqueries
Aggregation Queries
I EXISTS R is true if and only if R is not empty
I s IN R is true if and only if s is equal to one of the
values in R (where, e.g., s is an attribute and R is a
unary (one-column) relation/expression)
I s > ALL R is true if and only if s is greater than every
value in R
I s > ANY R is true if and only if s is greater than at
least one value in R
I we can use any other comparison operator instead of
> above
 Database
Conditions involving relations Management

Peter Wood
There are a number of SQL operators that apply to a
relation (or expression) R and return a Boolean result: SQL queries
SQL Subqueries
Aggregation Queries
I EXISTS R is true if and only if R is not empty
I s IN R is true if and only if s is equal to one of the
values in R (where, e.g., s is an attribute and R is a
unary (one-column) relation/expression)
I s > ALL R is true if and only if s is greater than every
value in R
I s > ANY R is true if and only if s is greater than at
least one value in R
I we can use any other comparison operator instead of
> above
I we can put NOT in front of IN to test if s is equal to
no value in R
 Database
Conditions involving relations Management

Peter Wood
There are a number of SQL operators that apply to a
relation (or expression) R and return a Boolean result: SQL queries
SQL Subqueries
Aggregation Queries
I EXISTS R is true if and only if R is not empty
I s IN R is true if and only if s is equal to one of the
values in R (where, e.g., s is an attribute and R is a
unary (one-column) relation/expression)
I s > ALL R is true if and only if s is greater than every
value in R
I s > ANY R is true if and only if s is greater than at
least one value in R
I we can use any other comparison operator instead of
> above
I we can put NOT in front of IN to test if s is equal to
no value in R
I EXISTS, ANY and ALL can be negated by putting
NOT in front of the whole expression
 Database
Using ALL Management

Peter Wood

SQL queries
SQL Subqueries
Aggregation Queries
Find the pub, beer and price for the cheapest beer sold
by any pub:
 Database
Using ALL Management

Peter Wood

SQL queries
SQL Subqueries
Aggregation Queries
Find the pub, beer and price for the cheapest beer sold
by any pub:

SELECT *
FROM Sells
WHERE price <= ALL
(SELECT price
FROM Sells);
 Database
Using ALL Management

Peter Wood

SQL queries
SQL Subqueries
Aggregation Queries
Find the pub, beer and price for the cheapest beer sold
by any pub:

SELECT *
FROM Sells
WHERE price <= ALL
(SELECT price
FROM Sells);

pub beer price

Horse and Hound Bad Habit 1.50
 Database
Using IN Management

Peter Wood

Find the beers sold by pubs visited by Bob: SQL queries

SQL Subqueries
Aggregation Queries
 Database
Using IN Management

Peter Wood

Find the beers sold by pubs visited by Bob: SQL queries

SQL Subqueries
Aggregation Queries

SELECT beer
FROM Sells
WHERE pub IN
(SELECT pub
FROM Visits
WHERE drinker = ’Bob’);
 Database
Using IN Management

Peter Wood

Find the beers sold by pubs visited by Bob: SQL queries

SQL Subqueries
Aggregation Queries

SELECT beer
FROM Sells
WHERE pub IN
(SELECT pub
FROM Visits
WHERE drinker = ’Bob’);

Previously we had:

SELECT beer
FROM Sells, Visits
WHERE drinker = ’Bob’
AND Sells.pub=Visits.pub;
 Database
Using IN for intersection Management

Peter Wood

SQL queries
MySQL does not support the INTERSECT operator SQL Subqueries
Aggregation Queries

So to find locations where both a drinker lives and a pub

is located:
 Database
Using IN for intersection Management

Peter Wood

SQL queries
MySQL does not support the INTERSECT operator SQL Subqueries
Aggregation Queries

So to find locations where both a drinker lives and a pub

is located:

(SELECT loc FROM Drinkers)

INTERSECT
(SELECT loc FROM Pubs);
 Database
Using IN for intersection Management

Peter Wood

SQL queries
MySQL does not support the INTERSECT operator SQL Subqueries
Aggregation Queries

So to find locations where both a drinker lives and a pub

is located:

(SELECT loc FROM Drinkers)

INTERSECT
(SELECT loc FROM Pubs);

we can use:

SELECT DISTINCT loc FROM Drinkers

WHERE loc IN
(SELECT loc FROM Pubs);
 Database
Using NOT IN for difference Management

Peter Wood

SQL queries
MySQL does not support the EXCEPT operator SQL Subqueries
Aggregation Queries

So to find locations where a drinker lives but no pub is

located:
 Database
Using NOT IN for difference Management

Peter Wood

SQL queries
MySQL does not support the EXCEPT operator SQL Subqueries
Aggregation Queries

So to find locations where a drinker lives but no pub is

located:

(SELECT loc FROM Drinkers)

EXCEPT
(SELECT loc FROM Pubs);
 Database
Using NOT IN for difference Management

Peter Wood

SQL queries
MySQL does not support the EXCEPT operator SQL Subqueries
Aggregation Queries

So to find locations where a drinker lives but no pub is

located:

(SELECT loc FROM Drinkers)

EXCEPT
(SELECT loc FROM Pubs);

we can use:

SELECT DISTINCT loc FROM Drinkers

WHERE loc NOT IN
(SELECT loc FROM Pubs);
 Database
Correlated subqueries Management

Peter Wood

SQL queries
SQL Subqueries
Aggregation Queries

I In the previous queries, the subquery could be

evaluated once, e.g. to find the pubs visited by Bob.
I This was because the subquery was independent of
the outer query.
I This is not the case when the subquery refers to a
tuple variable in the outer query.
I In such a case, we have what is known as a
correlated subquery.
 Database
Example of correlated subquery Management

Peter Wood

Find the names of drinkers who live where no pub is SQL queries
located: SQL Subqueries
Aggregation Queries
 Database
Example of correlated subquery Management

Peter Wood

Find the names of drinkers who live where no pub is SQL queries
located: SQL Subqueries
Aggregation Queries

SELECT name
FROM Drinkers
WHERE NOT EXISTS
(SELECT name
FROM Pubs
WHERE location=Drinkers.location);
 Database
Example of correlated subquery Management

Peter Wood

Find the names of drinkers who live where no pub is SQL queries
located: SQL Subqueries
Aggregation Queries

SELECT name
FROM Drinkers
WHERE NOT EXISTS
(SELECT name
FROM Pubs
WHERE location=Drinkers.location);

Subquery refers to the tuple variable (or relation) Drinkers

in the outer query.
 Database
Example of correlated subquery Management

Peter Wood

Find the names of drinkers who live where no pub is SQL queries
located: SQL Subqueries
Aggregation Queries

SELECT name
FROM Drinkers
WHERE NOT EXISTS
(SELECT name
FROM Pubs
WHERE location=Drinkers.location);

Subquery refers to the tuple variable (or relation) Drinkers

in the outer query.

name
Eve
 Database
Aggregation Operators Management

Peter Wood

SQL queries
SQL Subqueries
Aggregation Queries

I sometimes we may want to “summarise” a number of

values in a column
I this can be done using aggregation operators
I SQL aggregation operators include
I SUM: produces the sum of values in a column
I AVG: produces the average of values in a column
I MIN and MAX: produce the smallest and largest
values, respectively, in a column
I COUNT: produces the number of (not necessarily
distinct) values in a column
 Database
Finding the minimum value Management

Peter Wood

SQL queries
SQL Subqueries

Find the price of the cheapest beer sold in Bloomsbury: Aggregation Queries
 Database
Finding the minimum value Management

Peter Wood

SQL queries
SQL Subqueries

Find the price of the cheapest beer sold in Bloomsbury: Aggregation Queries

SELECT MIN(price) AS minPrice

FROM Sells
WHERE pub IN
(SELECT name
FROM Pubs
WHERE location=’Bloomsbury’);
 Database
Finding the minimum value Management

Peter Wood

SQL queries
SQL Subqueries

Find the price of the cheapest beer sold in Bloomsbury: Aggregation Queries

SELECT MIN(price) AS minPrice

FROM Sells
WHERE pub IN
(SELECT name
FROM Pubs
WHERE location=’Bloomsbury’);

minPrice
1.50
 Database
Counting tuples Management

Peter Wood

SQL queries
SQL Subqueries
Aggregation Queries

Find the number of people who visit the March Hare:

 Database
Counting tuples Management

Peter Wood

SQL queries
SQL Subqueries
Aggregation Queries

Find the number of people who visit the March Hare:

SELECT COUNT(drinker) AS numberVisiting

FROM Visits
WHERE pub=’March Hare’;
 Database
Counting tuples Management

Peter Wood

SQL queries
SQL Subqueries
Aggregation Queries

Find the number of people who visit the March Hare:

SELECT COUNT(drinker) AS numberVisiting

FROM Visits
WHERE pub=’March Hare’;

numberVisiting
2
 Database
Counting tuples Management

Peter Wood

SQL queries
SQL Subqueries
Aggregation Queries

Find the number of people who visit the March Hare:

SELECT COUNT(drinker) AS numberVisiting

FROM Visits
WHERE pub=’March Hare’;

numberVisiting
2

Could also use COUNT(*) to count the number of rows.

 Database
Grouping Management

Peter Wood
I Sometimes we don’t want an aggregation applied to
SQL queries
an entire column. SQL Subqueries
Aggregation Queries
I Instead we want to group the tuples of a relation into
groups based on the value of some attribute.
I E.g., we can group Sells tuples according to pub
value.
 Database
Grouping Management

Peter Wood
I Sometimes we don’t want an aggregation applied to
SQL queries
an entire column. SQL Subqueries
Aggregation Queries
I Instead we want to group the tuples of a relation into
groups based on the value of some attribute.
I E.g., we can group Sells tuples according to pub
value.
pub beer price
Horse and Hound Bad Habit 1.50
Horse and Hound Rampant Ram 2.00
Hound and Hare Shining Wit 2.75
Hound and Hare Rampant Ram 2.50
March Hare Bad Habit 1.75
March Hare Rampant Ram 2.50
Black Horse Bad Habit 2.50
Black Horse Shining Wit 2.25
Black Horse Rampant Ram 2.50
White Horse Rampant Ram 2.75
 Database
Grouping example Management

Peter Wood

SQL queries
Find the average price of the beer sold in each pub: SQL Subqueries
Aggregation Queries
 Database
Grouping example Management

Peter Wood

SQL queries
Find the average price of the beer sold in each pub: SQL Subqueries
Aggregation Queries

SELECT pub, AVG(price) AS avgPrice

FROM Sells
GROUP BY pub;
 Database
Grouping example Management

Peter Wood

SQL queries
Find the average price of the beer sold in each pub: SQL Subqueries
Aggregation Queries

SELECT pub, AVG(price) AS avgPrice

FROM Sells
GROUP BY pub;

pub avgPrice
Horse and Hound 1.75
Hound and Hare 2.675
March Hare 2.125
Black Horse 2.416
White Horse 2.75
 Database
Grouping example Management

Peter Wood

SQL queries
Find the average price of the beer sold in each pub: SQL Subqueries
Aggregation Queries

SELECT pub, AVG(price) AS avgPrice

FROM Sells
GROUP BY pub;

pub avgPrice
Horse and Hound 1.75
Hound and Hare 2.675
March Hare 2.125
Black Horse 2.416
White Horse 2.75

Each pub appears once in the answer.

 Database
Selecting Groups Management

Peter Wood

SQL queries
Find the average price of the beer sold in each pub that SQL Subqueries
Aggregation Queries

sells at least two beers:

 Database
Selecting Groups Management

Peter Wood

SQL queries
Find the average price of the beer sold in each pub that SQL Subqueries
Aggregation Queries

sells at least two beers:

SELECT pub, AVG(price) AS avgPrice

FROM Sells
GROUP BY pub
HAVING COUNT(beer) > 1;
 Database
Selecting Groups Management

Peter Wood

SQL queries
Find the average price of the beer sold in each pub that SQL Subqueries
Aggregation Queries

sells at least two beers:

SELECT pub, AVG(price) AS avgPrice

FROM Sells
GROUP BY pub
HAVING COUNT(beer) > 1;

pub avgPrice
Horse and Hound 1.75
Hound and Hare 2.675
March Hare 2.125
Black Horse 2.416
 Database
Grouping, Aggregation and Nulls Management

Peter Wood

SQL queries
SQL Subqueries
Aggregation Queries

I The value NULL is ignored in any aggregation.

I But NULL is treated as an ordinary value when
forming groups.
I When we perform any aggregation other than
COUNT over an empty set (bag) of values, the result
is NULL. The COUNT of an empty set (bag) is 0.
 Database
Ordering the output Management

Peter Wood

SQL queries
SQL Subqueries
Aggregation Queries

I we can ask for the tuples in the output to be sorted

I by using ORDER BY clause after any WHERE,
GROUP BY or HAVING clauses
I ORDER BY is followed by a list of attributes
I ordering is by default ascending (ASC) but we can
specify DESC after any attribute for descending order
 Database
Other Comparison Operators Management

Peter Wood

SQL queries
SQL Subqueries
Aggregation Queries

I IN, e.g., a IN (b1, b2, . . . )

I BETWEEN, e.g., a BETWEEN b and c
I LIKE — see next slide
I REGEX, e.g., a REGEX b — a matches pattern b,
where b uses regular expression syntax (see online)
 Database
String Pattern Matching with LIKE Management

Peter Wood
Which pubs have names that include the string ’Hare’?:
SQL queries
SQL Subqueries
Aggregation Queries
 Database
String Pattern Matching with LIKE Management

Peter Wood
Which pubs have names that include the string ’Hare’?:
SQL queries
SQL Subqueries
SELECT name Aggregation Queries

FROM Pubs
WHERE name LIKE ’%Hare%’
 Database
String Pattern Matching with LIKE Management

Peter Wood
Which pubs have names that include the string ’Hare’?:
SQL queries
SQL Subqueries
SELECT name Aggregation Queries

FROM Pubs
WHERE name LIKE ’%Hare%’

name location
Horse and Hound Bloomsbury
Hound and Hare Islington
March Hare Bloomsbury
Black Horse Islington
White Horse Bloomsbury

⇓
 Database
String Pattern Matching with LIKE Management

Peter Wood
Which pubs have names that include the string ’Hare’?:
SQL queries
SQL Subqueries
SELECT name Aggregation Queries

FROM Pubs
WHERE name LIKE ’%Hare%’

name location
Horse and Hound Bloomsbury
Hound and Hare Islington
March Hare Bloomsbury
Black Horse Islington
White Horse Bloomsbury

⇓
name
Hound and Hare
March Hare
 Database
References Management

Peter Wood

SQL queries
SQL Subqueries
Aggregation Queries

I Chapter 6 of [CB10]
I Chapters 3 and 4 of [SKS11]
I Chapter 6 of [UW08]