MySQL Subquery

Introduction to MySQL Subquery

In MySQL, a Subquery is defined as a SELECT SQL Statement that is used inside another SQL statement to calculate the results of outer queries. Simply in SQL, a Subquery is an inner query which is placed within an outer SQL query using different SQL clauses like WHERE, FROM, HAVING and with statement keywords such as SELECT, INSERT, FROM, UPDATE, DELETE, SET or DO, accompanied with expressional operators or logical operators.

Following are the types of Subquery with respective result details:

1. Single Row Subquery: It either returns zero or a single row
2. Multiple Row Subquery: It returns one or multiple rows of a table
3. Multiple Column Subquery: It returns one or multiple columns
4. Correlated Subqueries: It refers to one or more columns in the outer SQL query.

Syntax of MySQL Subquery

We can say that a Subquery or Inner Query is a SQL query that can be used as a condition for main SQL query outside that helps to restrict the data rows to be fetched from the tables embedded within the WHERE clause. This query could not be bounded in a set function.

For example:

SELECT * FROM tablename1 WHERE column1 = ANY (SELECT column1 FROM tablename2 WHERE tablename2……). So, here MySQL looks outside the subquery and finds tablename1 in the outer query.

Thus, like JOINS, a Subquery is used to combine table data from different tables into a single result set. But JOINS returns rows and Subquery returns either a single value as a result set or a row set. Hence, the main use of Subquery can be to compute an instant value for another query to be executed.

Working of MySQL Subquery

Let us explain to you how to use the MySQL Subquery to write any compound queries and also learn the correlated Subquery concept.

In MySQL, we can write Subquery anywhere where expression SQL query is used but it is surrounded within parenthesis.

Suppose, we have the following SQL query which provides us the persons who work in IT sector located in Delhi.

SELECT PersonName FROM PersonDb WHERE PersonID IN (SELECT PersonID FROM PersonIT WHERE City=’Delhi’);

In the above SQL Statement, first of all, the subquery returns all the PersonID of PersonIT located in city Delhi. Then the outer query selects all the names from PersonDb table who work in PersonIT whose PersonID is matched from the result set of the inner query. We can say that when we run this query then the query inside outer executes first and the result returned by it is used as an input for outer query.

Types of MySQL Subquery

Following are the types of Subquery with syntaxes and examples:

1. MySQL subquery on WHERE Clause

Let us prepare a demo data database table named Customer having information about customers and payment amount.

The table have fields CustomerID, Name, Payment_purpose, CustomerNum, Amount.

CREATE TABLE Customers ( CustomerID int NOT NULL PRIMARY KEY, Name varchar(255), CustomerNum int, Payment_purpose varchar(255), Amount int );

And also inserting some data sample as follows:

INSERT INTO Customers (CustomerID, Name, CustomerNum, Payment_purpose , Amount) VALUES ('1', 'Nikhil', '21', 'Rent', '4000');

We have used the following query to fetch the details required using a Subquery with comparison operators to return a value and with the expression in the WHERE clause:

SELECT Name, CustomerNum, Payment_purpose FROM Customers WHERE amount = (SELECT MAX(amount) FROM Customers);

Output:

This subquery with WHERE clause returns the customer details whose payment amount is maximum in the table Customer.

In addition to this, you can also execute subquery using equality operator and other comparison like greater than (>) or smaller than (<).

For example, let us find out the customer details from table whose payment amount is greater than average payment using a subquery.

SELECT Name, CustomerNum, Payment_purpose FROM Customers WHERE amount > (SELECT AVG(amount) FROM Customers);

Here, we have used the AVG aggregate function in SQL inner query statement which produces the following results through outer query.

Output:

2. MySQL Subquery with operators IN and NOT IN

When a subquery provides more than a single value, you can add other operators with WHERE clause such as IN and NOT IN.

We have two tables suppose ‘Customers’ (CustomerID, Name, Payment_purpose, CustomerNum, Amount) and ‘Orders’ (OrderID, OrderNum, Status, CustomerNum).

CREATE TABLE Orders (OrderID int NOT NULL PRIMARY KEY, CustomerNum int, status varchar (255), OrderNum int, Budget int );

We will first create the table, then insert some demo data.

Now let us find the customers who have not placed any orders using the subquery with NOT IN operator.

SELECT Name, CustomerNum FROM Customers WHERE CustomerNum NOT IN (SELECT DISTINCT CustomerNum FROM Orders);

Output:

3. MySQL subquery with FROM clause

When we use a subquery with FROM clause, the result set returned is considered as a temporary table rows which is then known as a derived or materialized subquery.

We can find the Minimum, Maximum and Average number of items from the Budget table too using the subquery.

SELECT I.CustomerID, I.NAME, I.Payment_purpose, I.Amount FROM (select AVG(Budget) as averageBudget from Orders) as Budget, Customers as I WHERE I.Amount > Budget.averageBudget;

Output:

4. MySQL Correlated Subquery

As mentioned above a correlated subquery is an independent query. It means that you can use it as a standalone query. It uses the result set data of outer query and evaluated for each row.

For example,

SELECT OrderNum , COUNT(OrderNum) AS items FROM Orders GROUP BY OrderNum;

Output:

5. MySQL subquery with EXISTS & NOT EXISTS

Using the EXISTS and NOT EXISTS operators in SQL subquery, the Boolean value is returned as result either TRUE or FALSE. The following is syntax to use EXISTS in subquery:

SELECT * (column1, …) FROM Tablename WHERE EXISTS (subquery);

For example,

SELECT Name FROM Customer C WHERE EXISTS (SELECT * FROM Orders WHERE CustomerNum = C.CustomerNum);

Output:

EXISTS and NOT EXISTS check whether the rows exist or not in the result set and returns TRUE or FALSE value respectively.

Conclusion

In MySQL, the subquery is nested inside WHERE clause of an outer SQL SELECT statement. The subquery can be added to further filter the result set using comparison operators like <,>, =, IN or BETWEEN, etc.

A Subquery executes once before the outer query so that the result is then applied for the outer query to produce the result set. The SQL Subquery can return a scalar, column, row and table subqueries in the result set rows which provides a condition for outer query and it gives the final required result from the table in MySQL.

Recommended Articles

This is a guide to MySQL Subquery. Here we discuss 5 different types of MySQL Subquery along with appropriate syntax and respective examples. You may also have a look at the following articles to learn more –

1. MySQL Aggregate Function
2. Cursor in MySQL
3. SQL Subquery | Examples
4. PostgreSQL Subquery | Examples
5. Guide to MySQL IN Operator
6. MySQL Timestamp | How to Works?
7. IF Statement in MySQL | How to Work?