MySQL Subqueries

In this tutorial, we will learn about how to write MySQL subqueries. A subquery is a query nested within another query. For instance, a subquery can be having a SELECT statement inside another SELECT statement. Multiple MySQL subqueries can be nested within a query. The subquery is called an Inner query and the main query is called the outer query. The inner query is executed first and then the main query gets executed. In case you have multiple subqueries then the innermost query gets executed first.

Syntax of MySQL Subqueries

There is no fixed syntax for MySQL subqueries. Instead, we have certain rules and semantics that need to be adhered to, to make a subquery in MySQL work. Subqueries are usually used with the SELECT, INSERT, UPDATE, and DELETE statements along with the operators like =, <, >, >=, <=; and clauses like WHERE, FROM, IN, BETWEEN, etc.

SELECT expression FROM table_name WHERE operator IN (SELECT expression FROM table_name WHERE condition);Code language: SQL (Structured Query Language) (sql)

It is important to note that this is a very generic syntax and you may have different clauses than what is mentioned above. ORDER BY clause cannot be used in a subquery. However, it can be used in the outermost main query.

Working of Subqueries in MySQL

Let us take an example of a subquery and have a walkthrough on it’s working. Consider the following Employee table.

And now let us execute the below subquery:

SELECT * FROM Employee WHERE eid IN (SELECT eid FROM Employee WHERE Salary>=80000);Code language: SQL (Structured Query Language) (sql)

The following infographic gives us a walkthrough of how the query gets executed.

As you can see, the inner query gets executed first and using the result-set of the inner query, the outer query gets evaluated. This is similar to how a math equation works.

Examples of MySQL Subqueries

In addition to the above Employee table, consider the below Office table.

Now, looking at the Office_Code column and the values in it, we can barely understand what it means. However, the Office table adds meaning to it. Now, let’s look at a few examples to understand the MySQL subqueries better.

1. MySQL Subquery Example Using SELECT Statement

Let us find out all the employees who are in the Kolkata and Chennai offices. We do so using the query,

SELECT * FROM Employee WHERE Office_Code IN (SELECT Office_Code FROM Office WHERE City IN ('Chennai', 'Kolkata'));Code language: SQL (Structured Query Language) (sql)

And we get the output as,

2. MySQL Subquery Example Using SELECT Statement Example 2

Let us find all the records of those employees who work in the offices in the state of Maharashtra. We do so using the query,

SELECT * FROM Employee WHERE Office_Code IN (SELECT Office_Code FROM Office WHERE State='Maharashtra');Code language: SQL (Structured Query Language) (sql)

And we get the output as,

3. Subquery Example Using UPDATE Statement

Suppose all employees in Kolkata have been asked to handle the Marketing of the company temporarily. We need to show in our Employee table that the Department has been reassigned for all the employees in the Kolkata office. We do so using,

UPDATE Employee SET Department = 'Marketing' WHERE Office_Code = (SELECT Office_Code FROM Office WHERE City='Kolkata');Code language: SQL (Structured Query Language) (sql)

This is followed by a SELECT Statement to display the result as follows,

4. Subquery Example Using DELETE Statement

Due to losses, the company has chosen to lay-off employees from the Kolkata office who earn less than 80,000. Using MySQL, we show this in our tables using the query,

DELETE FROM Employee WHERE Salary<80000 AND Office_Code = (SELECT Office_Code FROM Office WHERE City='Kolkata');Code language: SQL (Structured Query Language) (sql)

This is followed by a SELECT Statement to display the result as follows,

Conclusions

Subqueries are useful alternatives to the complex unions and joins in MySQL. Subqueries enable your queries to be structured and easily readable and understandable. For further reading, do check out the references.

References

• MySQL Official Documentation on Subqueries.
• JournalDev Article on Subqueries.