Common Table Expressions (WITH) - clear queries

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:

1WITH drogie_ksiazki AS (
2  SELECT tytul, cena FROM ksiazki WHERE cena > 50
3)
4SELECT * 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.