SQL Queries Using WITH Clause

Introduction

• Presenter: Taufik
• Focus: Writing SQL queries using the WITH clause (CTE - Common Table Expression)
• Topics Covered:
  • Syntax and execution of queries with WITH clause
  • Advantages of using WITH clause
  • Scenarios for using WITH clause

Key Concepts

• WITH Clause: Also known as CTE (Common Table Expression) or Subquery Factoring.
• Allows for better readability and structure in complex SQL queries.

Example Queries

1. Employees Table

• Goal: Fetch employees with a salary greater than the average salary.
• Employee Table Structure:
  • Employee ID
  • Employee Name
  • Employee Salary

Query Steps:

1. Calculate Average Salary:
   • Use AVG() function to get average salary.
   • Store result using WITH clause.
2. Main Query:
   • Select employees where salary is greater than the calculated average salary.

SQL Syntax Example:

WITH avg_salary AS (
    SELECT AVG(salary) AS average_salary
    FROM employee
)
SELECT *
FROM employee e
WHERE e.salary > (SELECT average_salary FROM avg_salary);

2. Sales Table

• Goal: Fetch stores whose sales are greater than average sales across all stores.
• Sales Table Structure:
  • Store ID
  • Store Name
  • Product
  • Quantity Sold
  • Cost

Query Steps:

1. Calculate Total Sales Per Store:
   • Use SUM(cost) and group by Store ID.
2. Calculate Average Sales:
   • Find the average of total sales from the first step.
3. Final Selection:
   • Select stores where total sales exceed average sales.

SQL Syntax Example:

WITH total_sales AS (
    SELECT store_id, SUM(cost) AS total_sales_per_store
    FROM sales
    GROUP BY store_id
),
average_sales AS (
    SELECT AVG(total_sales_per_store) AS avg_sales
    FROM total_sales
)
SELECT ts.store_id, ts.total_sales_per_store
FROM total_sales ts, average_sales av
WHERE ts.total_sales_per_store > av.avg_sales;

Advantages of Using WITH Clause

1. Improved Readability: Makes complex SQL queries easier to understand and maintain.
2. Performance Improvement: SQL executes CTEs once, and can reuse the data, reducing redundancy in processing.
3. Handling Recursive Queries: Simplifies writing of recursive SQL queries.

Ideal Scenarios for Using WITH Clause

• When a subquery is used multiple times, use WITH to avoid redundancy.
• In complex queries where clarity is required, breaking down into sections is beneficial.
• To improve performance by filtering large datasets early in the execution process.

Conclusion

• The WITH clause enhances SQL query structure, readability, and performance.
• Encouraged to subscribe for more SQL content, including advanced topics like recursive queries.