Lesson 10 of 11 · 26 min
Rewriting queries for performance
By the end of this lesson you can rewrite a slow query into an equivalent faster one (same result, cheaper plan): make filters sargable, replace correlated subqueries with a pre-aggregated join, choose between EXISTS, IN and JOIN correctly, and avoid the NOT IN NULL trap.
The first rule of rewriting: the result must not change. Before you compare plans, compare answers. A quick test is to run both versions and check that (old EXCEPT new) and (new EXCEPT old) are both empty, and that the row counts match (EXCEPT ignores duplicates). Then use EXPLAIN to confirm the new plan really does less work.
The rewrites below are the ones that pay off most often.
This lesson, its 4 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons