MySQL Subqueries

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);

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.

MySQL Subqueries Employee Table
Employee Table

And now let us execute the below subquery:

SELECT * FROM Employee WHERE eid IN (SELECT eid FROM Employee WHERE Salary>=80000);

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

Working Of mysql Subqueries

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.

Subqueries Office Table
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'));

And we get the output as,

Subqueries Select Example 1

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');

And we get the output as,

Subqueries Select Example 2

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');

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

Subqueries Update Example

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');

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

Subqueries Delete Example

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