SQL and MySQL course Β· Module 10: Transactions and advanced SQL
Common Table Expressions (WITH) - clear queries
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:
- first we define what "expensive books" are,
- 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. Which keyword does a CTE (Common Table Expression) begin with?
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 ...