Subqueries

A query placed inside another: scalar subquery, list subquery with IN and ANY, EXISTS and NOT EXISTS, derived table in FROM, correlated or not, and the traps of NOT IN with NULLs.

25 cards
6 topics
Free

Study this deck

The outline, topic by topic

Each topic is weighted the way the exam weighs it.

Concepts
Questions
Sort and link
The numbers
Scenarios
Understand

A few cards from the deck

Scalar subquery: what is it?
Show the answer
A SELECT in parentheses that returns one column and at most one row: the engine uses it as a plain value, for instance to the right of a > or an =.
duree > (SELECT AVG(duree) FROM film) compares each film with an average computed once. If it returns several rows, the database refuses: more than one row returned by a subquery used as an expression.
The film table holds Dune (155 min), Alien (117), Amélie (122), Titanic (195), Matrix (136) and Intouchables (112). Which rows does this query return?

```sql
SELECT titre FROM film
WHERE duree > (SELECT AVG(duree) FROM film);
`
- Dune and Titanic
- Dune, Titanic and Matrix
- Dune only
- Alien, Amélie and Intouchables
Show the answer
Dune and Titanic
The average is 837 / 6 = 139.5 minutes. Only Dune (155) and Titanic (195) exceed it; Matrix, at 136, stays below.
A query in parentheses placed to the right of an = must return …; to accept several, write IN instead.
Show the answer
A query in parentheses placed to the right of an = must return at most one row; to accept several, write IN instead.
Otherwise the database stops with more than one row returned by a subquery used as an expression. With zero rows the value is NULL and the comparison is never true.
List subquery: what is it?
Show the answer
A query in parentheses that returns one column of several rows, compared with a value using IN, NOT IN, ANY or ALL.
WHERE id IN (SELECT id_client FROM commande) keeps the customers whose id is in the list. IN is the same as = ANY, and > ALL means “greater than every value”.

Study this deck