Lesson 27 of 30

EXISTS, FROM and correlated subqueries

Today at the studio

The questions are now "who has at least one" and "who beats their own team's average". Each row needs its own little investigation. Byte takes this very personally.

The idea

EXISTS (subquery) is true when the subquery returns at least one row, and NOT EXISTS when it returns none. A subquery can look at the current row of the outer query: that is a correlated subquery, and it runs once for each outer row.

A subquery in FROM works like a temporary table: SELECT AVG(total) FROM (SELECT ... GROUP BY ...). It lets you aggregate an aggregate. Give it a name only if you need to refer to it.

Example

SELECT name FROM departments AS d
WHERE EXISTS (SELECT 1 FROM staff WHERE staff.department_id = d.id)

Your turn

0 of 4 questions

Question 1 of 4

What is the average total of merch units sold per show? Total per show first, then the average of those totals.