The world’s leading publication for data science, AI, and ML professionals.

1 Trick That Changed the Way I Wrote Queries Forever

Leverage Common Table Expressions to Simplify the Writing and Troubleshooting Complex Queries

Photo by Jan Antonin Kolar on Unsplash
Photo by Jan Antonin Kolar on Unsplash

What is a Common Table Expression

When writing complex queries, it’s often useful to break them up into smaller chunks for readability and debugging. Common Table Expressions or CTEs provide the ability to do this, and I’ve found them to be one of the most useful tools in my Sql toolbox.

CTEs are very simple to implement. They start with a simple WITH statement, the name of the new CTE that you’re going to SELECT. They start like this:

WITH
    cte_name AS (
        SELECT
            ...
    )

The beauty is that you can chain multiple CTEs together as many as you’d like. Let’s look at what a couple of them would look like.

WITH
    cte_name AS (
        SELECT
            ...
    ),

another_cte AS (
    SELECT * FROM foo
    JOIN cte_name ON cte_name.id = foo.id
)

SELECT * FROM another_cte
LIMIT 10

That illustrates the concept well. The first CTE runs the first query and stores it in memory called cte_name, and the second CTE joins the cte_name table to the foo table in the second CTE. You can use this pattern in multiple ways, but it simplifies constructing a complex query by breaking it down into logical parts.

Note: One small thing to note is where the , is after the first CTE separates each table.

Finally, you complete the process by running a standalone SELECT statement on the resulting CTE.

Of course, the power is to run much more complex logic. Each CTE can contain many SELECT statements, JOIN statements, WHERE clauses, etc. Use them to structure your query for readability and understandability.

Tip: For easy debugging or building your query, you can test each of the CTEs by simply commenting out the rest of the code and running a select after each of them. Like this.

WITH
    cte_name AS (
        SELECT
            ...
    ) --, Make sure to comment out the comma

SELECT * FROM cte_name
LIMIT 10

-- another_cte AS (
--     SELECT * FROM foo
--     JOIN cte_name ON cte_name.id = foo.id
-- )

-- SELECT * FROM another_cte
-- LIMIT 10

Example in Real Life

I wrote a query for a view I was creating in Snowflake. Without CTEs, this would have proven to be much more difficult.

WITH DAILY as (
    SELECT ID
    FROM "LOGS_DAILY"),
MAP AS (
    SELECT SOURCE_ID AS ID, ANY_VALUE(UUID) AS UUID
    FROM "CONTACT_MAP"
    WHERE SOURCE_ID_NAME = 'ID'
    AND DT = (SELECT MAX(DT) FROM "CONTACT_MAP")
    GROUP BY SOURCE_ID),
CONTACT AS (
    SELECT CONTACT_UUID, SITE_UUID
    FROM "CONTACT_MASTER"
    WHERE DT = (SELECT MAX(DT) FROM "CONTACT_MASTER")),
ACCOUNT AS (
    SELECT *
    FROM "ACCOUNT"
    WHERE SITE_STATUS = 'Active')
SELECT DISTINCT *
FROM DAILY
LEFT JOIN MAP ON MAP.ID = DAILY.ID
LEFT JOIN CONTACT ON CONTACT.CONTACT_UUID = MAP.CONTACT_UUID
LEFT JOIN ACCOUNT ON ACCOUNT.SITE_UUID = CONTACT.SITE_UUID
LIMIT 100

Conclusion

Common Table Expressions or CTEs are a powerful tool in your Querying toolbox that allows you to take complex, layered SELECT statements, break them down into more manageable chunks, and then pull them back together in the end. If you’re not using them today, give them a try, and I’m confident they will be a regular goto for you!

If you enjoy reading stories like these and want to support me as a writer, consider signing up to become a Medium member. It’s $5 a month, giving you unlimited access to thousands of articles. If you sign up using my link, I’ll earn a small commission with no extra cost to you.


Related Articles