💡Query Revolution: Unleashing the Potential of CTE’s and Nested Methods with Effortless Conversion Tips 🔍

In the dynamic realm of database querying, the choice between Common Table Expressions (CTE’s) and Nested Methods plays a pivotal role in determining the efficiency and performance of your queries. This article delves into the nuances of these two approaches, aiming to guide you towards optimal query performance.

A Comprehensive Guide to Choosing Between CTEs and Nested Queries in SQL

Easy tips for converting from CTE’s method to Nested method

I’m utilizing two datasets, namely “orders” and “order details.”

For example query: Show list of orders occurring from midnight before 12pm

query = """
    SELECT *
    FROM orders
    WHERE strftime('%H:%M:%S', order_date) >= '00:00:00'
        AND strftime('%H:%M:%S', order_date) < '24:00:00';
"""

sql_run(query)

# CTE's
query = """
WITH TAB AS (
    select *
    from orders
    where strftime('%H', order_date) in ('08','09','10','11')
) 
select * from TAB ;
"""


sql_run(query)

# Nested
query = """
select * from (
    select *
    from orders
    where strftime('%H', order_date) in ('08','09','10','11')
) ;
"""


sql_run(query)

From the initial query, it is transformed into CTE’s and then further modified from CTE’s into Nested. Here are the step-by-step tips for effortlessly converting from CTE’s to Nested:

1. Drag the code within the brackets and then cut it

2. Drag the text “TAB”

3. Then paste it at the TAB location or after the word “from”

4. Delete the text “WITH TAB AS”

5. The CTE’s method has been successfully transformed into Nested

- CTE’s: The main query defines it using the keyword “WITH.”
- Nested: Uses “FROM” within “FROM.”

Here are other queries that can be used to explore the data above:

1. Count the orders from each company with the status REJECTED per day

query = """
    SELECT company, order_date, COUNT(*) AS rejected_order_count
    FROM orders
    WHERE status = 'REJECTED'
    GROUP BY company, order_date;
"""
sql_run(query)

# CTE's
query = """
WITH TAB AS (    
    SELECT company, order_date, COUNT(*) AS rejected_order_count
    FROM orders
    WHERE status = 'REJECTED'
    GROUP BY company, order_date
)
SELECT * FROM TAB ;
"""
sql_run(query)

# Nested
query = """
SELECT * FROM (    
    SELECT company, order_date, COUNT(*) AS rejected_order_count
    FROM orders
    WHERE status = 'REJECTED'
    GROUP BY company, order_date
);
"""
sql_run(query)

2. Group the orders by hour of order_date

query = """
    SELECT 
        strftime('%H', order_date) AS hour,
        COUNT(*) AS order_count
    FROM orders
    GROUP BY hour
    ORDER BY hour;
"""
sql_run(query)

query = """
select count(distinct order_id) as total_orders, strftime('%H', order_date) as hour
from orders
group by 2
order by 2 DESC;
"""
sql_run(query)

# CTE's
query = """
WITH TAB AS (
select count(distinct order_id) as total_orders, strftime('%H', order_date) as hour
from orders
group by 2
order by 2 DESC
)
SELECT * FROM TAB ;
"""
sql_run(query)

# Nested
query = """
SELECT * FROM (
select count(distinct order_id) as total_orders, strftime('%H', order_date) as hour
from orders
group by 2
order by 2 DESC
);

"""
sql_run(query)

3. Group the orders by day and statuses as the example below: must load columns: date, total_order_set, total_order_rejected

query = """
    SELECT 
        DATE(order_date) AS date,
        SUM(CASE WHEN status = 'SENT' THEN 1 ELSE 0 END) AS total_order_set,
        SUM(CASE WHEN status = 'REJECTED' THEN 1 ELSE 0 END) AS total_order_rejected
    FROM orders
    GROUP BY date
    ORDER BY date;
"""
sql_run(query)

# CTE
query = """
WITH TAB AS (
    SELECT 
        DATE(order_date) AS date,
        SUM(CASE WHEN status = 'SENT' THEN 1 ELSE 0 END) AS total_order_set,
        SUM(CASE WHEN status = 'REJECTED' THEN 1 ELSE 0 END) AS total_order_rejected
    FROM orders
    GROUP BY date
    ORDER BY date
)
SELECT * FROM TAB;
"""
sql_run(query)

# Nested
query = """
SELECT * FROM (
    SELECT 
        DATE(order_date) AS date,
        SUM(CASE WHEN status = 'SENT' THEN 1 ELSE 0 END) AS total_order_set,
        SUM(CASE WHEN status = 'REJECTED' THEN 1 ELSE 0 END) AS total_order_rejected
    FROM orders
    GROUP BY date
    ORDER BY date
);
 
"""
sql_run(query)

4. Show list of orders occurring in February 2020 with SENT status

query = """
    SELECT *
    FROM orders
    WHERE strftime('%Y-%m', order_date) = '2020-02' AND status = 'SENT';
"""
sql_run(query)

# CTE
query = """
WITH TAB AS (
    SELECT *
    FROM orders
    WHERE strftime('%Y-%m', order_date) = '2020-02' AND status = 'SENT'
)
SELECT * FROM TAB ;
"""
sql_run(query)

# Nested
query = """
SELECT * FROM (
    SELECT *
    FROM orders
    WHERE strftime('%Y-%m', order_date) = '2020-02' AND status = 'SENT'
) ;

"""
sql_run(query)

5. Show a list of only the last orders from each company

query = """
    SELECT *
    FROM (
        SELECT *,
               ROW_NUMBER() OVER (PARTITION BY company ORDER BY order_date DESC) AS row_num
        FROM orders
    ) AS ranked_orders
    WHERE row_num = 1;
"""
sql_run(query)

# CTE's
query = """
WITH TAB AS (
    SELECT *
    FROM (
        SELECT *,
               ROW_NUMBER() OVER (PARTITION BY company ORDER BY order_date DESC) AS row_num
        FROM orders
    ) AS ranked_orders
    WHERE row_num = 1
)
SELECT * FROM TAB ;
"""
sql_run(query)

# Nested
query = """
SELECT * FROM  (
    SELECT *
    FROM (
        SELECT *,
               ROW_NUMBER() OVER (PARTITION BY company ORDER BY order_date DESC) AS row_num
        FROM orders
    ) AS ranked_orders
    WHERE row_num = 1
) ;

"""
sql_run(query)

For further details and continuation, kindly check this repository: https://github.com/noviawulandar1/SQL-CTE-s-Nested

Conclusion:
As you embark on the journey of mastering the art of choosing between CTE’s and Nested Methods, remember that there is no one-size-fits-all solution. Each approach has its merits, and your decision should align with the unique demands of your database architecture and application requirements. By staying informed and agile in your query optimization strategies, you can achieve optimal performance and maintain a codebase that stands the test of time.

If you have any questions or want to engage in discussions, feel free to reach out. Your curiosity and insights are always welcome. Happy querying!