MySQL Subquery

Introduction

MySQL is an open-source RDBMS (Relational Database Management System) used to store and retrieve relational data with the help of SQL (Structured Querying Language). They are widely utilized to build robust databases where data is inter-related.

It is crucial to efficiently extract and manipulate relational data in relational databases. Therefore, efficient management of data can be carried out using a flexible and powerful feature of MySQL which is known as Subqueries.

Subquery or nested query or inner query is a feature where one SQL query is structured or written within another query. Whenever a complete query is written with an inner query then the inner query will be executed first and its output will act as input for the outer query.

The comparison operators like <, >, =, <=>, etc., are used with subqueries in MySQL to return either true or false by comparing values. These comparison operators compare the result returned by the inner query or subquery with the result returned by the main query.

Generally, the subquery in MySQL is allowed to be written on the right side of the comparison operator. They are allowed to be written on either the left side or right side of the main query.

Subquery in MySQL breaks down complex queries or problems into more manageable or simpler ones. They break down intricate problems into smaller ones which aid in the flexibility of queries. Using subqueries helps in retrieving data from one table based on the results of another query, conditions, calculations, etc.

Subqueries in MySQL can be classified into two types:

• Correlated subqueries
• Non-correlated subqueries

Correlated subqueries in MySQL are those types of subqueries that are evaluated for each row of the outer query. Correlated subqueries are dependent on the outer query for their execution. On the contrary,

Non-correlated subqueries are those types of subqueries that are evaluated once to generate output that is utilized by the outer query. Non-correlated subqueries are independent, they do not depend upon an outer query for their execution.

Subquery in MySQL offers versatility to accomplish a wide range of tasks such as aggregating data, filtering data based on conditions, retrieving data, etc. Subqueries enhance the performance of queries along with optimizing their structure.

MySQL Subquery Syntax

Subquery in MySQL has a straightforward and user-friendly syntax for the creation of inner queries or subqueries.

Subqueries are created by enclosing the inner query or subquery within a parenthesis which can be further used by the outer query as input. The subqueries in MySQL can be inculcated into specific parts like the WHERE clause, SELECT statement, HAVING clause, or FROM clause of the main query.

Let's take a look at the syntax used for writing subquery in MySQL:

MySQL Subquery Example

After understanding a lot about the introduction and significance of subqueries in MySQL, let's now move towards understanding subqueries practically with the help of an example.

Example

In this example, we will consider a table product_info having information like, product_id, product_name, and price. We will retrieve the information of products having a higher price than the average price of all products which are present in the table. Here, we will use a subquery to compare each product's price to the average price of products in the table.

Consider a table product_info

Query:

Output

Explanation

In the above example, we have structured a SQL query having both main and subquery in it. Since we know that the subquery part will be executed and then it is used by the main query as input.

Here, the subquery is used to calculate the average price of all the products in the table. The main query selects the product_id and products_name from the product_info table. The WHERE clause specifies the condition based on which the products which are having a higher price than the average price will be included in the output table.

MySQL Subquery with Comparison Operator

The comparison operators like <, >, =, <=>, etc., are used with subqueries in MySQL to return either true or false by comparing values. These comparison operators compare the result returned by the inner query or subquery with the result returned by the main query. The Subqueries which return a single value as output can be placed on either the left side or right side of the comparison operator.

Let's take an example to understand more about using subqueries with comparison operators.

Example

In this example, we will consider a table employee having information like employee_id, employee_dept, and emp_salary. Here, that employee information will be retrieved whose salary is the maximum among all the employees in the table irrespective of the department.

Let's consider the following table employee:

Query

Output

Explanation

In the above example, we have structured a query to find out the information of an employee who has the maximum salary among the other employees. As we know, the subquery will execute before the main or outer query and it will return the maximum value in the salary column using the MAX aggregate function.

The value or result returned by the subquery will be compared with the values in the main query using the = equality operator. Therefore, when the whole query gets executed it will return the information of that employee whose salary is maximum.

MySQL Subquery with IN or NOT-IN Operator

IN or NOT IN comparison operators are used to filter results in MySQL. The IN operator is used to filter or include the result set that matches a subquery result.

The NOT IN comparison operator is used to filter or exclude rows from a result set. It is used to invalidate a condition.

Both the IN and NOT IN operator has to be used with the WHERE clause.

Let's understand the use of both operators separately with subqueries.

Example(IN)

In this example, we will find out customers who reside in a particular city by considering a table customer having information like customer_id, customer_name, customer_city. For this, we will use the IN operator with a subquery which will filter and include the result set that matches the result produced by the subquery.

Let's consider the following table customer

Query

Output

Explanation

In the above example, we have retrieved the details of customers who reside in Dallas. The subquery returns the customer_id of customers residing in Dallas. The output or result set returned by the subquery is used by the main query. The outer query returns the customer_id and customer_city based on the comparision done by the IN operator with the WHERE clause.

Example(NOT IN)

In this example, we will consider the same table used in the above example to find out who is residing where except in a particular city. For this, we will use the NOT IN operator with a subquery that will filter and exclude the result set that matches the result produced by the subquery.

Let's consider the same table customer used in the above example.

Query

Output

Explanation

In the above example, we have retrieved the details of those customers who do not reside in Dallas. The subquery returns the customer_id of customers residing anywhere other than Dallas. The output or result set returned by the subquery is used by the main query. The outer query returns the customer_id and customer_city based on the comparison done by the NOT IN operator with the WHERE clause which excludes the matching result as seen in the output table.

MySQL Subquery in the FROM Clause

The FROM clause in MySQL is used to select a table from which the results are retrieved. Here, the subquery used in the FROM clause will return a temporary table which is used by the outer query as an input from which it can retrieve information.

The table returned by the subquery used in the FROM clause is called a temporary table or derived table.

Let's understand using a subquery in the FROM clause with an example.

Example

In this example, we will find out the maximum and minimum number of products in the shopping table. The maximum and minimum numbers will be calculated using the MAX and MIN aggregate functions respectively.

Let's consider the following table product:

Query

Output

Explanation

In the above example, we have calculated the Maximum and Minimum products using aggregate functions. Here, the subquery used in the FROM clause returns a temporary table having an alias as prods. From the temporary table, the outer query retrieves the max and min values. The result set or table returned by the outer query is aliased as list.

MySQL Correlated Subqueries

As discussed earlier, correlated subqueries in MySQL are those types of subqueries that are evaluated for each row of the outer query. Correlated subqueries are dependent on the outer query for their execution.

Let's understand the concept of correlated subqueries in detail with an example.

Example

In this example, we will retrieve information on employees having salaries higher than the average salary in the same department. The result set will contain information about employees concerning the department.

Let's consider the following table employee

Query

Output

Explanation

In the above example, we have fetched the info of employees who have higher salaries than the average salary in their respective departments. The subquery will return the average salary of employees in respective departments.

The average salary will be compared with each employee's salary using the > operator. Those who have a higher salary than the average salary in each department will be produced in the result set as seen in the output.

MySQL Subqueries with EXISTS or NOT EXISTS

EXISTS And NOT EXISTS operators are boolean operators which return either true or false. The EXISTS operator in MySQL checks whether a particular data is present or not. If the subquery returns any result set or any value then the operator returns true otherwise false.

On the contrary, the NOT EXISTS operator works in the opposite of the EXISTS operator means it outputs true if the subquery does not return any result or value and outputs false if any value is returned by the subquery.

Both the operators have to be used with correlated subqueries.

Let's understand EXISTS and NOT EXISTS with an example.

Example(EXISTS)

In this example, we will consider two tables one is the employee table and the other is the products table. Here, we will fetch the details of those employees having at least a single order using the EXIST operator.

Let's consider both tables Employee and Products

Employee

Products

Query

Output

Explanation

In the above example, we checked whether an employee have at least one purchase or order, and based on that the information of employees got retrieved. In the subquery, it returns the product info along with the employee_id of those employees who are present in both tables. The EXISTS operator will check whether any employee purchased any product or not and if they have purchased then the operator will true. After which the outer query retrieves the information of those employees who have at least a single purchase.

Example(NOT EXISTS)

In this example, we will consider two tables one is the employee table and the other is the products table. Here, we will fetch the details of those employees having no purchases using the NOT EXIST operator.

Let's consider the same tables used in the above example.

Query

Output

Explanation

In the above example, we have retrieved the information of employees having no purchases. In the subquery, it returns the product info along with the employee_id of those employees who are present in both tables. The NOT EXISTS operator will check whether any employee purchased any product or not and if they have not purchased anything then the operator will return true. After which the outer query retrieves the information of employees having no purchases.

MySQL ROW Subqueries

Row Subqueries are those type of subqueries that returns a single value or row and more than one column value. Row subquery in MySQL can be compared using different comparison operators like >=, <=, =, !=.

Let's understand ROW subqueries with an example.

Example

In this example, we will retrieve the details of products which has the lowest cost in their respective category. Here, we will fetch the required information by using the ROW subquery in MySQL.

Let's consider the following table products:

Query

Output

Explanation

In the above example, we have retrieved the product's information which has the lowest price in their respective category. For this, inside the subquery, we used the MIN() aggregate function to find the minimum price and the GROUP BY clause to group them according to the category. After the WHERE clause, we used the ROW keyword to compare the combination of rows at a single time that is (product_price, category). The outer query compares and shows information like product_name and category in the output result set.

MySQL Subqueries with ALL, ANY, and SOME

ALL, ANY, and SOME are keywords that are used after the comparison operator in a query. In MySQL, the ALL keyword is used to compare results or values with the values returned by subqueries. If the comparison of all values is true then it returns true.

The ANY keyword is used to compare values with any value returned by the subquery. If the comparison is true for any value returned by subqueries then ANY returns true.

SOME keyword works in the same way as the ANY keyword. It also returns true if any comparison is true.