DEV Community

Cover image for Common Table Expressions (CTEs) in SQL
John Kyalo
John Kyalo

Posted on

1 1 1

Common Table Expressions (CTEs) in SQL

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

Image of Timescale

Timescale – the developer's data platform for modern apps, built on PostgreSQL

Timescale Cloud is PostgreSQL optimized for speed, scale, and performance. Over 3 million IoT, AI, crypto, and dev tool apps are powered by Timescale. Try it free today! No credit card required.

Try free

Top comments (0)

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more

👋 Kindness is contagious

Dive into an ocean of knowledge with this thought-provoking post, revered deeply within the supportive DEV Community. Developers of all levels are welcome to join and enhance our collective intelligence.

Saying a simple "thank you" can brighten someone's day. Share your gratitude in the comments below!

On DEV, sharing ideas eases our path and fortifies our community connections. Found this helpful? Sending a quick thanks to the author can be profoundly valued.

Okay