SQL and MySQL course Β· Module 10: Transactions and advanced SQL

Common Table Expressions (WITH) - clear queries

1 min read Β·
In this lesson3

When a query grows long and nested, it is easy to get lost in it. A CTE (Common Table Expression) comes to the rescue - a way to name a temporary result before using it further.

The WITH syntax

A CTE begins with the keyword WITH:

WITH drogie_ksiazki AS (
  SELECT tytul, cena FROM ksiazki WHERE cena > 50
)
SELECT * FROM drogie_ksiazki;

We create a temporary "shelf" named drogie_ksiazki, and then refer to it in the main query - just like an ordinary table. After the query runs, the CTE disappears.

Why is this better?

Compare it with a subquery nested in the middle - a CTE reads top to bottom, like a story:

  1. first we define what "expensive books" are,
  2. then we use them.

A CTE is not a view

  • A view is saved in the database permanently (CREATE VIEW).
  • A CTE lives for just one query - it is the Keeper's momentary note.

A CTE works great for complex reports, where several steps build on top of one another. Thanks to it, even a complicated query stays clear.

Spotted a mistake in this lesson?

Check yourself

Answer the questions from this lesson. Pick an answer to see right away whether it is correct.

  1. 1. Which keyword does a CTE (Common Table Expression) begin with?

  2. 2. How does a CTE differ from a view?

These are 2 of 3 questions for this lesson. Solve the rest in the game.

Hands-on tasks in the game

  • Code editor

    Write a CTE named drogie_ksiazki that selects tytul and cena from the ksiazki table for rows where cena > 50. Then, in the main query, retrieve all columns from drogie_ksiazki. Use the syntax WITH drogie_ksiazki AS (SELECT ...) SELECT * FROM drogie_ksiazki;

  • Vertical ordering

    Arrange the lines of the CTE expression in the correct order (top to bottom):

  • Code editor

    Write a CTE named czytelnicy_krakowa that selects imie and nazwisko from the czytelnicy table for rows where miasto = 'Krakow'. Then, in the main query, retrieve all columns from czytelnicy_krakowa. Use the syntax WITH ... AS (SELECT ...) SELECT * FROM ...

  1. QuestionCTE keyword
  2. QuestionCTE versus a view
  3. Coding taskA CTE of expensive books
  4. Put in orderLines of the drogie_ksiazki CTE
  5. Coding taskA CTE of readers
  6. QuestionWhy use a CTE

Useful articles