SQL Lesson 4: Subqueries and CTEs

SheetDrill SQL SQL Lesson 4 · Subqueries and CTEs

SQL · Lesson 4

Ctrl/⌘ + Enter to run

✓

SQL Lesson 4 done.

You can put a query inside another one, filter with IN and NOT IN without falling into the NULL trap, tell COUNT(*) from COUNT(column), and break a long query into steps with WITH. That covers reading data. Next: designing the tables yourself.

Next: design a database →

What you're learning

A subquery is a query in parentheses inside another one. If it returns one value you can compare with it: WHERE amount > (SELECT AVG(amount) FROM orders). If it returns a list, use IN or NOT IN. Careful: one NULL in a NOT IN list and you get no rows, so filter NULLs out or use NOT EXISTS.

WITH name AS (…) (a CTE) gives a query a name so the next query can use it like a table. It's the same as a subquery, written top to bottom.