Supporting reference and preserved earlier practice. Open the current classroom lesson →

SUPPORTING SQL REFERENCE · LESSON 18

Lesson 18: Connect matching records

Combine JOINs and grouped movie summaries, and explain the order of query operations.

Practice this lesson · 2 exercises ↓Related class labs ↓

Practice database: Pixar movies & box office. Recovered teaching snapshot. Explore the live schema below before choosing your columns.

Read the explanation, then use the practice section below. Generic syntax and illustrative tables explain the idea; the exercise schema shows the exact names available in Pixar movies & box office.

Now that we have an idea of all the parts of a query, we can now talk about how they all fit together in the context of a complete query.

Complete SELECT query

SELECT

DISTINCT column, AGG_FUNC(column_or_expression), ...

FROM

mytable

JOIN

another_table

mytable.column = another_table.column

WHERE

constraint_expression

GROUP BY

column

HAVING

constraint_expression

ORDER BY

column ASC/DESC

LIMIT

count OFFSET count;

This is the logical order used to reason about a query, not a guaranteed physical execution sequence. The optimizer can use indexes, reorder operations, and avoid materializing intermediate tables while preserving the result.

Query Order of Execution

1. `FROM` and `JOIN`s

The FROM clause, and subsequent JOINs are first executed to determine the total working set of data that is being queried. This includes subqueries in this clause, and can cause temporary tables to be created under the hood containing all the columns and rows of the tables being joined.

2. `WHERE`

Once we have the total working set of data, the first-pass WHERE constraints are applied to the individual rows, and rows that do not satisfy the constraint are discarded. Each of the constraints can only access columns directly from the tables requested in the FROM clause. Aliases in theSELECT part of the query are not accessible in most databases since they may include expressions dependent on parts of the query that have not yet executed.

3. `GROUP BY`

The remaining rows after the WHERE constraints are applied are then grouped based on common values in the column specified in the GROUP BY clause. As a result of the grouping, there will only be as many rows as there are unique values in that column. Implicitly, this means that you should only need to use this when you have aggregate functions in your query.

4. `HAVING`

If the query has a GROUP BY clause, then the constraints in the HAVING clause are then applied to the grouped rows, discard the grouped rows that don't satisfy the constraint. Like the WHEREclause, aliases are also not accessible from this step in most databases.

5. `SELECT`

Any expressions in the SELECT part of the query are finally computed.

6. `DISTINCT`

Of the remaining rows, duplicate combinations across all selected columns are removed by DISTINCT will be discarded.

7. `ORDER BY`

If an order is specified by the ORDER BY clause, the rows are then sorted by the specified data in either ascending or descending order. Since all the expressions in the SELECT part of the query have been computed, you can reference aliases in this clause.

8. `LIMIT` / `OFFSET`

Finally, the rows that outside the range specified by the LIMIT and OFFSET are discarded, leaving the final set of rows to be returned from the query.

Conclusion

Not every query needs to have all the parts we listed above, but a part of why SQL is so flexible is that it allows developers and data analysts to quickly manipulate data without having to write additional code, all just by using the above clauses.

Exercise

Here ends our lessons on SELECT queries, congrats of making it this far! This exercise will try and test your understanding of queries, so don't be discouraged if you find them challenging. Just try your best.

Try a worked example

SELECT director, COUNT(id) as Num_movies_directed
FROM movies
GROUP BY director;

Read the query, predict its result, then inspect the rows or schema it produces.

Open example in playground ↗

TRY IT YOURSELF

Lecture 12: Order of Execution of a Query

Pixar movies & box office · Answer questions across tables

Recovered teaching snapshot. Explore the live schema below before choosing your columns.

Opening your saved work…

YOUR LESSON DATABASE

Tables, keys & sample rows

0 tables · Updates after your query

PK = primary key · FK = declared foreign key. Expand a table to see its columns and the first five stored rows.

Loading schema…

Table & column names only · ↑ ↓ choose · Tab insert · Esc dismiss

Query results

Run a query to explore. Check my answer checks the selected exercise.

Read-only practice · One statement at a time · Ctrl / ⌘ + Enter runs your query

Query Order of Execution

1. FROM and JOINs

2. WHERE

3. GROUP BY

4. HAVING

5. SELECT

6. DISTINCT

7. ORDER BY

8. LIMIT / OFFSET