SQL — SubQuery vs CTEs

Differences, examples on SubQuery vs Common Table Expressions (CTE)

Image by Author

In SQL, both Subqueries and Common Table Expressions (CTEs) are used to perform complex queries or manipulate data. However differences in terms of syntax and usage.

First, let's explore each Subquery and CTEs with examples.

1. SubQueries:

Subquery, also known as a nested query, is a query nested within another query. It allows you to retrieve data based on the results of another query.

Subqueries can be used in various parts of SQL statements, such as the SELECT ,FROM ,WHERE orJOIN clauses. Here’s an example:

SELECT 
    employee_id,  
    total_sales
FROM 
    (SELECT 
        employee_id, 
        SUM(sales_amount) AS total_sales
    FROM 
        sales
    GROUP BY 
        employee_id
    )
WHERE 
    total_sales > 10000

In the above example, (SELECT employee_id, SUM(sales_amount) AS total_sales FROM sales GROUP BY employee_id) calculate’s total_sales amount done by each employee. The outer query then retrieves the employee IDs and total sales of employees who generated above 10,000 in sales.

Subqueries are beneficial when you need to filter or retrieve data based on the results of another query. They can be used to perform calculations, filtering or joining operations within a single SQL statement.

2. Common Table Expressions (CTEs):

CTE is a temporary named result set that you can reference within a SQL statement. It allows you to break down complex queries into smaller, more manageable parts.

CTEs are defined using the WITH keyword and can be used in subsequent queries just like regular tables. Here’s an example:

WITH employee_sales AS 
(
    SELECT 
        employee_id, 
        SUM(sales_amount) AS total_sales
    FROM 
        sales
    GROUP BY 
        employee_id
)
SELECT 
    employee_id, 
    total_sales
FROM 
    employee_sales
WHERE 
    total_sales > 10000

In the above example, employee_sales is defined, which calculates the total sales for each employee from the sales table. Then, the main query references the employee_sales CTE and retrieves the employee IDs and total sales of employees who generated above 10,000 in sales.

CTEs are particularly useful when you need to reuse the same result set multiple times within the query or when you want to simplify complex queries by dividing them into logical parts.

Conclusion:

Subqueries and CTEs serve a similar purpose as you can see in the above examples, but differ in syntax and usage.

CTEs are defined using WITH keyword and can be referenced multiple times in a query, while Subqueries are nested queries used within other queries. Both techniques are powerful and can help you write complex SQL queries efficiently.

In the actual world, several tables must be combined to form a single data model. In a single data model, performed calculations are referred to several times. Subqueries and CTEs both aid in the efficient writing of queries. Personally, I like CTEs because they make queries easier to write and more readable.

I hope this blog is helpful for you and hopefully, it helps to understand the difference between Subqueries and CTEs.