SQL Nested Subqueries Tutorial 🎯

Welcome to our comprehensive guide on SQL Nested Subqueries! In this lesson, we'll delve into the fascinating world of subqueries, where one SQL query is nested inside another. Let's get started! 📝

Understanding Subqueries 💡

A subquery is a query that is nested inside another SQL query. It allows you to return a result set from one query and use it in the WHERE, FROM, or JOIN clause of another query.

Single-Row Subqueries 📝

A single-row subquery returns only one row. It is used in the WHERE clause to filter records based on the result of the subquery.

sql

-- Example: Finding employees who earn more than the highest-paid employee
SELECT * FROM Employees
WHERE Salary > (SELECT MAX(Salary) FROM Employees);

Nested Subqueries 💡

A nested subquery is a subquery within another subquery. The outer subquery uses the result of the inner subquery.

sql

-- Example: Finding all departments with at least three employees
SELECT Department FROM Employees
WHERE DepartmentId IN (
  SELECT DepartmentId FROM Employees
  GROUP BY DepartmentId
  HAVING COUNT(EmployeeId) >= 3
);

Advanced Nested Subqueries 💡

Advanced nested subqueries can get complex, but they are incredibly powerful. Here's an example of a correlated subquery, where the outer query references the inner query.

sql

-- Example: Finding employees who earn more than their manager
SELECT e1.EmployeeName, e1.Salary
FROM Employees e1
WHERE e1.Salary > (
  SELECT e2.Salary
  FROM Employees e2
  WHERE e2.ManagerId = e1.EmployeeId
);

SQL Nested Subqueries Tutorial 🎯

SQL Nested Subqueries Tutorial 🎯

Understanding Subqueries 💡

Single-Row Subqueries 📝

Nested Subqueries 💡

Advanced Nested Subqueries 💡

Quiz 📝