MYSQL Subquery

A subquery is embedded inside another query and acts as input or output for that query. Subqueries are also called inner queries and they can be used in various complex operations in SQL.

Subqueries help in executing queries with dependency on the output of another query. Subqueries are enclosed in parentheses. In this article, we will see how we can write and use subqueries in MYSQL.

MYSQL Subquery

MYSQL Subquery can be used with an outer query which is used as input to the outer query. It can be used with SELECT, FROM, and WHERE clauses. MYSQL subquery is executed first before the execution of the outer query.

Let's Setup an Environment

Before writing queries let's create simple tables for performing operations. We will create two tables Employee and Departments.

CREATE TABLE Employee(
    empid numeric(10),
    name varchar(20),
    salary numeric(10),
    department varchar(20)
);

CREATE TABLE Departments(
    deptid numeric(10),
    department varchar(20)
);

Let's add some values into these tables.

INSERT INTO Employee 
VALUES (100,"Jacob A",20000,"SALES"),(101,"James T",50000,"IT"),(102,"Riya S",30000,"IT");

INSERT INTO Departments 
VALUES (1,"IT"),(2,"ACCOUNTS"),(3,"SUPPORT");

Employee Table:

Departments Table:

MYSQL Subquery with WHERE Clause

Let's select employees from department with the department id as 1.

SELECT * 
FROM Employee 
WHERE department=(SELECT department FROM Departments WHERE deptid=1);

Output:

Explanation: The query selects all columns from the Employee table where the department matches the department retrieved from the Departments table with deptid equal to 1.

MYSQL Subquery with comparison operators

Less than operator (<)

Let's select employees whose salary is less than average salary of all employees.

SELECT * 
FROM Employee 
WHERE salary < (SELECT avg(salary) from Employee)

Output:

Explanation: The query selects all columns from the Employee table where the salary is less than the average salary calculated from the salaries of all employees.

Greater than or equal to operator (>=)

Lets select employees whose salary is greater than or equal to average salary of all employees.

SELECT * 
FROM Employee 
WHERE salary >= (SELECT avg(salary) from Employee);

Output:

Explanation: In this query nested query calculates average salary which is used by outer query. Similary we can use other comparison operators in MYSQL.

MYSQL Subquery with IN and NOT IN operators

IN operator

Lets select all employees whose department is in departments table.

SELECT * 
FROM Employee 
WHERE department IN (SELECT department FROM Departments);

Output:

Explanation: The query selects all columns from the Employee table where the department matches any department retrieved from the Departments table, using a subquery to obtain all existing departments.

NOT IN operator

Lets select all employees whose department is not in department table.

SELECT * 
FROM Employee 
WHERE department NOT IN (SELECT department FROM Departments);

Output:

Explanation: The query selects all columns from the Employee table where the department does not match any department retrieved from the Departments table, using a subquery to obtain existing departments.

MYSQL Subquery with FROM clause

lets select all departments from employee table with nested query.

SELECT department 
FROM (SELECT * from Employee) as A;

Output:

Explanation: here the subquery will return all colums from which outer query will select only department.

MYSQL Correlated Subquery

Correlated subquery is the one which uses columns from outer table for execution.

Lets select EmpId and Name of employee from Employee where salary is less than average salary and deparment is same as outer table.

SELECT empid , name 
FROM Employee AS A 
WHERE salary < ( SELECT avg(salary) from Employee AS B WHERE A.department = B.department);

Output:

Explanation: This query will first fetch average salary depending on department name in two tables and then select employees with salary less than average salary.

MYSQL Subquery with EXISTS and NOT EXISTS

EXISTS

Lets select employees for which there exists atleast 1 department where department of employee is same as department in departments.

SELECT empid , name
FROM Employee
WHERE EXISTS (SELECT 1 FROM Departments WHERE Departments.department = Employee.department);

Output:

Explanation: This query will select at least one row from departments where department name is same as employees department name and then return employee id and name of corresponding employee.

NOT EXISTS

Let's select employees for which there does not exist at least 1 department where department of employee is same as department in departments.

SELECT empid , name
FROM Employee
WHERE NOT EXISTS (SELECT 1 FROM Departments WHERE Departments.department = Employee.department);

Output:

Explanation: The query retrieves employee ID and name from the Employee table where no department in the Departments table matches the employee's department, checking for non-existence of such departments using a subquery.

Conclusion

Thus we have seen what is subqueries or inner queries or nested queries in MYSQL . We have also seen how we can use subqueries with various SQL clauses. Various clauses can be used in subqueries to perform different operations. Operators can be used to perform comparison based on values outputted from inner query. These queries can be used with more complex clauses to perform more advanced operations.