DEV Community

stuxnat
stuxnat

Posted on • Updated on

CTE in SQL

One thing I like about SQL are Common Table Expressions (CTE). CTE were introduced to simplify SQL queries.

A CTE allows for the creation of a temporary result set, which can be treated just like a view. You can perform JOIN operations on them and the results can be filtered. This allows you to separately define queries instead of writing nested queries. This makes performing operations on datasets easier, especially when working with complex queries. This also helps to make the code more readable.

The syntax is as follows:

Define the CTE name and columns.

WITH cteName (column1, column2)  
AS 
Enter fullscreen mode Exit fullscreen mode

Define the CTE query.

WITH cteName (column1, column2)  
AS (  
    SELECT 1, 2 
   )
Enter fullscreen mode Exit fullscreen mode

Define the outer query, which will reference the CTE name.

WITH cteName (column1, column2)  
AS (  
    SELECT 1, 2 
   ) 
SELECT * FROM cteName
Enter fullscreen mode Exit fullscreen mode

Here is an example of a sqlzoo problem solved with CTE instead of nested queries.

8. For each customer show: Company name, contact name, number of calls where the number of calls is fewer than 5

with b as (
        SELECT  *
        FROM Caller
),


a as (
        SELECT
            Customer.Company_name,
            Customer.Contact_id,
            COUNT(*) AS nc
        FROM
            Customer
            JOIN
                Caller
                ON (Customer.Company_ref = Caller.Company_ref)
            JOIN
                Issue
                ON (Caller.Caller_id = Issue.Caller_id)
        GROUP BY
            Customer.Company_name,
            Customer.Contact_id
        HAVING
            COUNT(*) < 5
)

SELECT
    a.Company_name,
    b.first_name,
    b.last_name,
    a.nc
    from b join a ON (a.Contact_id = b.Caller_id);
Enter fullscreen mode Exit fullscreen mode

Here is the rest of the series on GitHub.

Discussion (0)