Here's a preview from my zine, Become a SELECT Star!! If you want to see more comics like this, sign up for my saturday comics newsletter or browse more comics!
browse more comics!
get the zine!
read the transcript!
Some questions can’t be answered with one Simple SQL query.
For example, this query finds owners who have named their dogs popular names: (“boring” owners :))
dogs:
| owner | name |
|---|---|
| ken | darcy |
| bob | darcy |
| bob | lassie |
| ahmed | darcy |
| sara | floof |
| sara | lassie |
SELECT owner
FROM dogs
WHERE name in SELECT name
FROM dogs
GROUP BY name
HAVING count(*) > 2)
the subquery evaluates to (‘darcy’)
query output:
| owner |
|---|
| ken |
| bob |
| ahmed |
common table expressions
“Common table expressions” (or CTEs) let you name a query so people reading it can understand what it’s for.
Here’s the query above rewritten using a CTE:
WITH popular_dog_names AS (
SELECT name
FROM dogs
GROUP BY name
HAVING count(*) > 2
)
SELECT owner
FROM dogs INNER JOIN popular_dog_names
ON dogs.name = popular_dog_names.name
Where you can use a. subquery/CTE
in a FROM
SELECT .....
FROM (<subquery or CTE>)
GROUP BY .....
in a WHERE
SELECT ...
WHERE name IN (<subquery>)
Saturday Morning Comics!
Want another comic like this in your email every Saturday? Sign up here!
