MySQL Subquery

A subquery in MySQL is a query, which is nested into another SQL query and embedded with SELECT, INSERT, UPDATE or DELETE statement along with the various operators. We can also nest the subquery with another subquery. A subquery is known as the inner query, and the query that contains subquery is known as the outer query. The inner query executed first gives the result to the outer query, and then the main/outer query will be performed. MySQLallows us to use subquery anywhere, but it must be closed within parenthesis. All subquery forms and operations supported by the SQL standard will be supported in MySQL also.

The following are the rules to use subqueries:

• Subqueries should always use in parentheses.
• If the main query does not have multiple columns for subquery, then a subquery can have only one column in the SELECT command.
• We can use various comparison operators with the subquery, such as >, <, =, IN, ANY, SOME, and ALL. A multiple-row operator is very useful when the subquery returns more than one row.
• We cannot use the ORDER BY clause in a subquery, although it can be used inside the main query.
• If we use a subquery in a set function, it cannot be immediately enclosed in a set function.

The following are the advantages of using subqueries:

• The subqueries make the queries in a structured form that allows us to isolate each part of a statement.
• The subqueries provide alternative ways to query the data from the table; otherwise, we need to use complex joins and unions.
• The subqueries are more readable than complex join or union statements.

MySQL Subquery Syntax

The following is the basic syntax to use the subquery in MySQL:

SELECT column_list (s) FROM  table_name 
 WHERE  column_name OPERATOR 
(SELECT column_list (s)  FROM table_name [WHERE])  

MySQL Subquery Example

Let us understand it with the help of an example. Suppose we have a table named “employees” that contains the following data:

Table: employees

Following is a simple SQL statement that returns the employee detail whose id matches in a subquery:

1. SELECT emp_name, city, income FROM employees   
2.    WHERE emp_id IN (SELECT emp_id FROM employees);  

This query will return the following output:

More Example :

the following query uses a subquery to return the employees who work in the offices located in the USA.

<code>SELECT 
    lastName, firstName
FROM
    employees
WHERE
    officeCode IN (SELECT 
            officeCode
        FROM
            offices
        WHERE
            country = 'USA');</code><small>Code language: SQL (Structured Query Language) (sql)</small>

In this example:

• The subquery returns all office codes of the offices located in the USA.
• The outer query selects the last name and first name of employees who work in the offices whose office codes are in the result set returned by the subquery.

When executing the query, MySQL evaluates the subquery first and uses the result of the subquery for the outer query.

MySQL Subquery with Comparison Operator

A comparison operator is an operator used to compare values and returns the result, either true or false. The following comparison operators are used in MySQL <, >, =, <>, <=>, etc. We can use the subquery before or after the comparison operators that return a single value. The returned value can be the arithmetic expression or a column function. After that, SQL compares the subquery results with the value on the other side of the comparison operator. The below example explains it more clearly:

Following is a simple SQLstatement that returns the employee detail whose income is more than 350000 with the help of subquery:

1. SELECT * FROM employees   
2. WHERE emp_id IN (SELECT emp_id FROM employees   
3.   WHERE income > 350000);  

This query first executes the subquery that returns the employee id whose income > 350000. Second, the main query will return the employees all details whose employee id are in the result set returned by the subquery. After executing the statement, we will get the below output, where we can see the employee detail whose income>350000.

Let us see an example of another comparison operator, such as equality (=) to find employee details with maximum income using a subquery.

1. SELECT emp_name, city, income FROM employees   
2.    WHERE income = (SELECT MAX(income) FROM employees);  

It will give the output where we can see two employees detail who have maximum income.

MySQL Subquery with IN or NOT-IN Operator

If the subquery produces more than one value, we need to use the IN or NOT IN operator with the WHERE clause. Suppose we have a table named “Student” and “Student2” that contains the following data:

Table: Student

Table: Student2

The following subquery with NOT IN operator returns the student detail who does not belong to Los Angeles City from both tables as follows:

1. SELECT Name, City FROM student  
2. WHERE City NOT IN (  
3. SELECT City FROM student2 WHERE City=’Los Angeles’);  

After execution, we can see that the result contains the student details not belonging to Los Angeles City.

MySQL Subquery in the FROM Clause

If we use a subquery in the FROM clause, MySQL will return the output from a subquery is used as a temporary table. We called this table as a derived table, inline views, or materialized subquery.

The following subquery returns the maximum, minimum, and average number of items in the order table:

1. SELECT Max(items), MIN(items), FLOOR(AVG(items))  
2. FROM  
3. (SELECT order_id, COUNT(order_id) AS items FROM orders  
4.  GROUP BY order_date) AS Student_order_detail;  

It will give the output as follows:

MySQL Correlated Subqueries

A correlated subquery in MySQL is a subquery that depends on the outer query. It uses the data from the outer query or contains a reference to a parent query that also appears in the outer query. MySQL evaluates it once from each row in the outer query.

1. SELECT emp_name, city, income  
2. FROM employees emp WHERE income > (   
3. SELECT AVG(income) FROM employees WHERE city = emp.city);  

In the above query, we select an employee name and city whose income is higher than the average income of all employees in each city.

The subquery executes for every city of the specified table because it will change for every row. Therefore, the average income will also be changed. Then, the main query filters employee detail whose income is higher than the average income from the subquery.

MySQL Subqueries with EXISTS or NOT EXISTS

The EXISTS operatoris a Boolean operator that returns either true or false result. It is used with a subquery and checks the existence of data in a subquery. If a subquery returns any record at all, this operator returns true. Otherwise, it will return false. The NOT EXISTS operator used for negation that gives true value when the subquery does not return any row. Otherwise, it returns false. Both EXISTS and NOT EXISTS used with correlated subqueries. The following example illustrates it more clearly. Suppose we have a table customer and order that contains the data as follows:

The below SQL statements uses EXISTS operator to find the name, occupation, and age of the customer who has placed at least one order.

1. SELECT name, occupation, age FROM customer C  
2. WHERE EXISTS (SELECT * FROM Orders O  
3. WHERE C.cust_id = O.cust_id);    

This statement uses NOT EXISTS operator that returns the customer details who have not placed an order.

1. SELECT name, occupation, age FROM customer C  
2. WHERE NOT EXISTS (SELECT * FROM Orders O  
3. WHERE C.cust_id = O.cust_id);    

We can see the below output to understand the above queries result.

MySQL ROW Subqueries

It is a subquery that returns a single row where we can get more than one column values. We can use the following operators for comparing row subqueries =, >, <, >=, <=, <>, !=, <=>. Let us see the following example:

1. SELECT * FROM customer C WHERE ROW(cust_id, occupation) = (  
2. SELECT order_id, order_date FROM Orders O WHERE C.cust_id = O.cust_id);  

If given row has cust_id, occupation values equal to the order_id, order_date values of any rows in the first table, the WHERE expression is TRUE, and each query returns those first table rows. Otherwise, the expression is FALSE, and the query produces an empty set, which can be shown in the below image:

MySQL Subqueries with ALL, ANY, and SOME

We can use a subquery which is followed by the keyword ALL, ANY, or SOME after a comparison operator. The following are the syntax to use subqueries with ALL, ANY, or SOME:

1. operand comparison_operator ANY (subquery)  
2. operand comparison_operator ALL (subquery)  
3. operand comparison_operator SOME (subquery)  

The ALL keyword compares values with the value returned by a subquery. Therefore, it returns TRUE if the comparison is TRUE for ALL of the values returned by a subquery. The ANY keyword returns TRUE if the comparison is TRUE for ANY of the values returned by a subquery. The ANY and SOME keywords are the same because they are the alias of each other. The following example explains it more clearly:

1. SELECT cust_id, name FROM customer WHERE   
2. cust_id > ANY (SELECT cust_id FROM Orders);  

We will get the output as follows:

If we use ALL in place of ANY, it will return TRUE when the comparison is TRUE for ALL values in the column returned by a subquery. For example:

1. SELECT cust_id, name FROM customer WHERE   
2. cust_id > ALL (SELECT cust_id FROM Orders);  

We can see the output as below: