Let me take you through an advanced yet easy concept to grasp in SQL.
You have probably already dealt with sub-queries in SQL. If so, then this is no difference.
A CTE is basically a named temporary result set used within a larger SQL statement.
Similar to a subquery also known as a nested query, CTEs are useful for breaking down complex queries into more manageable parts to improve code readability.
Think of it as a better way to organize longer queries.
Having known that, let's go through a CTE example:
First things first, the syntax to include a CTE statement is,
WITH cte_xxxx
AS (larger/temporary query)
then now the main query
A point to note is every other time, you should run the two together because as its name appears, a temporary query is not saved anywhere
WITH cte_employees
AS (
SELECT emp_id, first_name, last_name, dpt_id, dpt_name
FROM employees)
SELECT * FROM cte_employees
WHERE dpt_id = 2;
The main query selects data from our CTE allowing easy retrieval of information specifically related to department 2
Always treat a CTE query like any other query...Go ahead and perform joins, aggregate functions in a CTE.
In the event of multiple CTEs, always include them in the same WITH statement separated by a comma.
Happy querying SQL nerds
Top comments (0)