SQL Subqueries Intro 🎯

Welcome to our SQL Subqueries tutorial! In this lesson, we'll explore a powerful feature of SQL that allows you to nest one SELECT statement within another. Let's dive in! 🤿

What are Subqueries? 💡

Subqueries are SELECT statements nested within other SQL statements like SELECT, INSERT, UPDATE, or DELETE. They are used to reuse the result set of one query as input to another query.

Let's take a simple example to understand this better. Suppose you want to find out the average salary of employees who work in the department with the highest average salary.

Subquery Syntax 📝

The basic syntax for a subquery is as follows:

sql

SELECT column_list
FROM table_name
WHERE column_name = (Subquery);

The subquery is enclosed within parentheses () and returns a single value (for equality comparison) or a list of values (for IN, ANY, ALL, etc. comparison).

Simple Subquery Example 📝

Let's create a simple table for this example:

sql

CREATE TABLE Employees (
  ID INT PRIMARY KEY,
  DepartmentID INT,
  Salary INT,
  Name VARCHAR(50)
);

INSERT INTO Employees VALUES
(1, 101, 5000, 'John'),
(2, 101, 5500, 'Jane'),
(3, 102, 4000, 'Doe'),
(4, 102, 4500, 'Alice');

Now, let's find the name of the employee who has the highest salary:

sql

SELECT Name
FROM Employees
WHERE Salary = (
  SELECT MAX(Salary) FROM Employees
);

Correlated Subqueries 💡

A correlated subquery is a subquery that references a column from the main query. The subquery is executed once for each row of the main query.

Let's find the average salary of each department:

sql

SELECT DepartmentID, AVG(Salary) AS AverageSalary
FROM Employees
GROUP BY DepartmentID;

Now, let's find the department with the highest average salary:

sql

SELECT DepartmentID, AVG(Salary) AS AverageSalary
FROM Employees
WHERE Salary = (
  SELECT AVG(Salary) FROM Employees
  WHERE DepartmentID = Employees.DepartmentID
);

Advanced Subquery Examples 📝

Example 1: Find employees who earn more than the average salary of their department

sql

SELECT *
FROM Employees
WHERE Salary > (
  SELECT AVG(Salary)
  FROM Employees
  WHERE DepartmentID = Employees.DepartmentID
);

Example 2: Find all departments that have at least one employee earning more than 5000

sql

SELECT *
FROM Departments
WHERE EXISTS (
  SELECT *
  FROM Employees
  WHERE Employees.DepartmentID = Departments.ID AND Salary > 5000
);

SQL Subqueries Intro 🎯

SQL Subqueries Intro 🎯

What are Subqueries? 💡

Subquery Syntax 📝

Simple Subquery Example 📝

Correlated Subqueries 💡

Advanced Subquery Examples 📝

Example 1: Find employees who earn more than the average salary of their department

Example 2: Find all departments that have at least one employee earning more than 5000

Quiz 📝