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:
.png)
Departments Table:
.png)
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:
.png)
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:
.png)
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:
.png)
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:
.png)
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:
.png)
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:
.png)
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:
.png)
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:
.png)
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:
.png)
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.
Similar Reads
SQL | Subquery
In SQL, subqueries are one of the most powerful and flexible tools for writing efficient queries. A subquery is essentially a query nested within another query, allowing users to perform operations that depend on the results of another query. This makes it invaluable for tasks such as filtering, cal
6 min read
PL/SQL Subqueries
PL/SQL subqueries are powerful SQL features that allow for the nesting of one query inside another for dynamic data retrieval. They have extensive applications when complex problems are to be solved by reducing them into smaller, more manageable queries. The inner query, usually called the subquery,
10 min read
SQL Join vs Subquery
The difference between SQL JOIN and subquery is that JOIN combines records of two or more tables whereas Subquery is a query nested in another query. SQL JOIN and Subquery are used to combine data from different tables simplifying complex queries into a single statement. Here we will discuss SQL JOI
4 min read
MySQL Outer Join
MySQL is a popular and strong system that is extensively employed for handling data in the field of relational databases. A crucial aspect of working with databases is the ability to retrieve and handle data from different tables. SQL joins combine rows from different tables by connecting them based
4 min read
MySQL Query Optimization
In any database-driven application, the efficiency of MySQL queries plays a crucial role in determining the overall performance. The MySQL query optimization involves improving the execution speed of the SQL queries to ensure faster response times and efficient resource utilization. This article exp
7 min read
MariaDB Subqueries
MariaDB utilizes SQL, a structured query language, as an open-source Relational database management system (RDBMS). It manages and manipulates data efficiently. One powerful function that contributes to the flexibility of MariaDB is using subqueries. This article will focus on MariaDB subqueries. We
5 min read
SQL Correlated Subqueries
In SQL, correlated subqueries are powerful tools that allow us to perform row-by-row comparisons and retrieve complex data. Unlike regular subqueries, correlated subqueries depend on values from the outer query, making them dynamic and highly effective for solving complex database problems. In this
5 min read
SQL Server Correlated Subquery
Correlate subquery is a great tool in SQL servers that allows users to fetch the required data from the tables without performing complex join operations. In SQL, a subquery is a query nested inside another query. A correlated subquery is a specific type of subquery that references columns from the
7 min read
MySQL SELF JOIN
Joins are very important for effective data retrieval and analysis. The 'JOIN' clause is used to combine data from two or more tables using the common column between them. In MySql, there are many types of joins like INNER JOIN, OUTER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, and SELF JOIN. In this ar
5 min read
MySQL Reserved Words
MySQL is a popular and widely used Relational Database Management System. Like any programming language, MySQL has its own set of reserved words. Reserved words are specific terms or keywords with predefined meanings within the database system. It is very important to know the reserved words to avoi
5 min read