SQL

🧠 Day 9: Mastering SQL Subqueries & CTE (Common Table Expressions)

Photo of Mathimaa Mathimaa3 weeks ago
0 1 2 minutes read

🧠 Day 9: Mastering SQL Subqueries & CTE (Common Table Expressions)

🔍 What is a Subquery?

A subquery is a query written inside another query.
It helps to fetch results dynamically based on conditions derived from another query.

📘 Definition:
A query within another query is called a Subquery.

📂 Types of Subqueries

Type Description
1️⃣ Single Row Subquery Returns a single column and single value.
2️⃣ Multiple Column Subquery Returns multiple columns from a table.
3️⃣ Inline View Subquery A subquery written inside the FROM clause.
4️⃣ Scalar Subquery A subquery written inside the SELECT clause.
5️⃣ Nested Subquery A subquery written inside the WHERE clause.
6️⃣ Correlated Subquery Inner query depends on the outer query.

⚙️ Execution Order

  • For most subqueries:
    ➤ Inner query executes first, then outer query.

  • For correlated subqueries:
    ➤ Outer query executes first, then inner query runs for each row.

🧩 1. Single Row Subquery

Fetch the employee with the highest salary.

SELECT *
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

🧩 2. Multiple Column Subquery

Find employees earning the maximum salary in each department.

SELECT *
FROM employees
WHERE (department_id, salary) IN
(SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id);

🧩 3. Inline View Subquery

A query inside the FROM clause acts like a temporary table.

SELECT MAX(sal)
FROM (SELECT department_id, MAX(salary) sal
FROM employees
GROUP BY department_id);

🧩 4. Scalar Subquery

A query inside the SELECT clause returns a single value.

SELECT first_name, salary,
(SELECT AVG(salary) FROM employees) AS avg_salary
FROM employees;

🧩 5. Correlated Subquery

Inner query depends on the outer query.
Executed once per row of the outer query.

SELECT *
FROM employees e
WHERE salary > (
SELECT AVG(salary)
FROM employees
WHERE department_id = e.department_id
);

🧩 6. Nth Max Salary (Department-wise)

Using DENSE_RANK():

SELECT *
FROM (
SELECT department_id, salary,
DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
FROM employees
)
WHERE rank = 5;

🧠 What is a CTE (Common Table Expression)?

A CTE creates a temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement.

Syntax:

WITH CTE_Name AS (
SELECT ...
)
SELECT * FROM CTE_Name;

💡 Example: Basic CTE

WITH dept_employees AS (
SELECT employee_id, first_name, salary
FROM employees
WHERE department_id = 10
)
SELECT *, AVG(salary) OVER () AS avg_salary_for_dept_10
FROM dept_employees;

💎 Find Nth Max Salary Using CTE

WITH ranked_salaries AS (
SELECT employee_id, salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees
)
SELECT employee_id, salary
FROM ranked_salaries
WHERE salary_rank = 3;

✅ Key Takeaways

  • Subqueries make SQL more dynamic and modular.

  • Correlated subqueries are useful but can be performance heavy.

  • CTEs improve readability and reusability of complex queries.

  • Use window functions like RANK() and DENSE_RANK() for ranking problems.

Photo of Mathimaa Mathimaa3 weeks ago
0 1 2 minutes read

Leave a Reply

Your email address will not be published. Required fields are marked *

Back to top button