Lecture 9: Subqueries Queries Within Queries
Dr Kieran T. Herley Department of Computer Science University College Cork
2017-2018
KH (03/11/17)
Lecture 9: Subqueries
2017-2018
1 / 23
Summary SQL’s set operators. Subqueries.
KH (03/11/17)
Lecture 9: Subqueries
2017-2018
2 / 23
Subqueries
Subqueries: queries built out of simpler queries often an alternative to join-based queries
Today’s DB: movies(id, title, yr, score, votes, director) actors(id, name) castings(movieid, actorid)
KH (03/11/17)
Lecture 9: Subqueries
2017-2018
3 / 23
Set Operations
Set Operators A ∪ B = {a, b, c, d, e, f , g , h} A ∩ B = {a, c} A − B = {b, d, f } Recall: A ∪ B (union) contains all elements that belong either to set A or to set B (or both) A ∩ B (intersection) contains all elements that belong both to set A and to set B A − B (difference) contains all elements that belong both to set A but not to set B
Since relational theory based on set theoretic notion like sets, relations etc., it seems natural that SQL should support set operators . . . KH (03/11/17)
Lecture 9: Subqueries
2017-2018
4 / 23
Set Operations
Subquery Intersection List films ids with both Humphrey Bogart and Katharine Hepburn Cunning Plan: Separately find HB’s films and those of KH Find those in common (intersect)
KH (03/11/17)
Lecture 9: Subqueries
2017-2018
5 / 23
Set Operations
Subquery Intersection List films ids with both Humphrey Bogart and Katharine Hepburn Cunning Plan: Separately find HB’s films and those of KH Find those in common (intersect)
( /∗ ids of films with HB ∗/ SELECT movieid FROM actors JOIN castings ON actor.id = castings . actorid WHERE actor.name = ’Humphrey Bogart’ ) INTERSECT ( /∗ ids of films with KH ∗/ . . . )
INTERSECT in ANSI standard but not supported by MySQL (nor is DIFFERENCE); unlike some other SQL systems KH (03/11/17)
Lecture 9: Subqueries
2017-2018
5 / 23
Set Operations
Subquery Unions List ids of all actors whose name is ’Jack’ or who have at least ten films to their credit UNION is supported by MySQL
KH (03/11/17)
Lecture 9: Subqueries
2017-2018
6 / 23
Set Operations
Subquery Unions List ids of all actors whose name is ’Jack’ or who have at least ten films to their credit UNION is supported by MySQL
( /∗ ids of actors named Jack ∗/ ) UNION ( /∗ ids of actors with at least ten films ∗/ );
Two subqueries involve tables actors and castings respectively For UNION, subquery results must be “compatible” i.e. have same number of columns and types KH (03/11/17)
Lecture 9: Subqueries
2017-2018
6 / 23
Set Operations
Unions cont’d
( /∗ ids of actors named Jack ∗/ SELECT id FROM actors WHERE name LIKE ’Jack%’ ) UNION ( /∗ ids of actors with at least ten films ∗/ SELECT actorid AS id FROM castings GROUP BY actorid HAVING COUNT(∗) >= 10 );
SQL normally allows duplicates, but for UNION duplicates are suppressed by default; use UNION ALL if you really want them KH (03/11/17)
Lecture 9: Subqueries
2017-2018
7 / 23
Set Operations
Cautionary Example List films that either released during the 1960s or have a score of at least 8.0 ( /∗ titles of films from 1960s ∗/ SELECT title FROM movies WHERE yr BETWEEN 1960 AND 1969 ) UNION ( /∗ titles of films with scores >= 8.0 ∗/ SELECT title FROM movies WHERE score >= 8.0 );
OK but . . .
KH (03/11/17)
Lecture 9: Subqueries
2017-2018
8 / 23
Set Operations
Cautionary Example List films that either released during the 1960s or have a score of at least 8.0 ( /∗ titles of films from 1960s ∗/ SELECT title FROM movies WHERE yr BETWEEN 1960 AND 1969 ) UNION ( /∗ titles of films with scores >= 8.0 ∗/ SELECT title FROM movies WHERE score >= 8.0 );
OK but . . . SELECT title FROM movies WHERE yr BETWEEN 1960 AND 1969 OR score >= 8.0; KH (03/11/17)
Lecture 9: Subqueries
2017-2018
8 / 23
Subqueries That Return Single Values
List The Film(s) With The Greatest Score If only we knew what the top score was . . .
KH (03/11/17)
Lecture 9: Subqueries
2017-2018
9 / 23
Subqueries That Return Single Values
List The Film(s) With The Greatest Score If only we knew what the top score was . . .
SELECT title, score FROM movies WHERE score = ( SELECT MAX(score) FROM movies );
Inner subquery (SELECT MAX(score) . . .) returns the maximum score: MAX(score) 9.0 a single value (albeit “wrapped” in a 1 × 1 table) Outer (containing) query uses this value in its WHERE clause KH (03/11/17)
Lecture 9: Subqueries
2017-2018
9 / 23
Subqueries That Return Single Values
List The Ids Of All Actors Appearing In “The Godfather”
Inner subquery extracts film’s id from movies table; outer query extracts associated actors from castings
SELECT actorid FROM castings WHERE movieid = ( SELECT id FROM movies WHERE title = ’Godfather, The’ );
KH (03/11/17)
Lecture 9: Subqueries
2017-2018
10 / 23
Subqueries That Return Single Values
What Does This Do?
SELECT title, score FROM movies WHERE score > ( SELECT score FROM movies WHERE title = ’Sound of Music, The’ );
KH (03/11/17)
Lecture 9: Subqueries
2017-2018
11 / 23
Subqueries Returning One-Column Tables
Conditions Involving Relations
Tables containing a single column are known as unary relations; such relations are effectively lists SQL provides some Boolean functions that operate on a unary relation (R): EXISTS R: True if R is not empty s IN R: True if s is one of the values in R (also s NOT IN R) s > ALL R: True if s is greater than each and every value in R s > ANY R: True if s is greater than any one value in R
The last two can use any comparing operator (=,
