Sub Query in SQL

A subquery, also known as an inner query or nested query, is a query within another SQL query. The subquery is executed first, and its result is passed to the main query (or outer query). Subqueries can be used in various clauses such as SELECT, FROM, WHERE, and HAVING.

Example: Let's use two tables: Employees and Departments.

SQL

-- Creating the Employees table
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(50),
    Age INT,
    Salary DECIMAL(10, 2),
    DepartmentID INT
);

-- Inserting the provided data
INSERT INTO Employees (EmployeeID, Name, Age, Salary, DepartmentID)
VALUES 
(1, 'Ashish', 24, 25000, 1),
(2, 'Kirshan', 32, 30000, 1),
(3, 'Anjali', 20, 25000, 2),
(4, 'Manjulika', 30, 15000, 1),
(5, 'Katrina', 37, 18000, 3),
(6, 'Esha', 37, 20000, 3);

Table: Employees

EmployeeID	Name	Age	Salary	DepartmentID
1	Ashish	24	25000	1
2	Kirshan	32	30000	1
3	Anjali	20	25000	2
4	Manjulika	30	15000	1
5	Katrina	37	18000	3
6	Esha	37	20000	3

SQL

-- Creating the Departments table
CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName VARCHAR(50)
);

-- Inserting the provided data
INSERT INTO Departments
VALUES 
(1, 'Electrical'),
(2, 'Electronics'),
(3, 'Computer');

Table: Departments

DepartmentID	DepartmentName
1	Electrical
2	Electronics
3	Computer

Subquery in WHERE Clause Find employees who belong to the "Electrical" department.

SQL

SELECT Name, Salary
FROM Employees
WHERE DepartmentID = (SELECT DepartmentID FROM Departments WHERE DepartmentName = 'Electrical');

The above query returns the following result:

Name	Salary
Ashish	25000
Kirshan	30000
Manjulika	15000

Explanation:

The subquery (SELECT DepartmentID FROM Departments WHERE DepartmentName = 'Electrical') retrieves the DepartmentID for "Electrical".
The outer query then retrieves employees whose DepartmentID matches the one returned by the subquery.

Subquery in SELECT Clause Find the name of the department for each employee.

SQL

SELECT Name,
(SELECT DepartmentName FROM Departments WHERE Departments.DepartmentID = Employees.DepartmentID) AS Department
FROM Employees;

Name	Department
Ashish	Electrical
Kirshan	Electrical
Anjali	Electronics
Manjulika	Electrical
Katrina	Computer
Esha	Computer

Explanation: • The subquery in the SELECT clause retrieves the DepartmentName for each employee using the DepartmentID from the Employees table.

Multiple-Row Subquery (Subquery with IN)

Find all employees who belong to either the "Electrical" or "Computer" departments.

SQL

SELECT Name, Salary
FROM Employees
WHERE DepartmentID IN (SELECT DepartmentID FROM Departments WHERE DepartmentName IN ('Electrical', 'Computer'));

Name	Salary
Ashish	25000
Kirshan	30000
Manjulika	15000
Katrina	18000
Esha	20000

Explanation:

The subquery retrieves the DepartmentID of the "Electrical" and "Computer" departments.
The outer query returns employees whose DepartmentID matches any of the IDs returned by the subquery.

Previous Next