Subqueries in MySQL

INTRODUCTION

 
In this tutorial, I am going to explain about MySQL Subqueries with examples.
 

SUBQUERY

 
In MySQL, a subquery is defined as a query used inside another query. In other words, subquery can be nested inside another query. It is also known as inner query and the query that contains the subquery (inner query) is known as outer query.
 
Sub-query is a SELECT statement used within a WHERE clause or having CLAUSE of a SQL statement. Usually a sub-query is executed first then a result  is returned to the main query. Based on the returned value the main query is executed. Sub-queries structure a complex query into isolated parts so that a complex query can be broken down into a series of logical steps for easy understanding and code maintenance
 
Writing Sub-queries: With the help of some examples we can use sub-queries.
 
To understand the concept of MySQL Subquery, let’s take a database and few tables with dummy data, which I have attached here also.
  1. CREATE DATABASE VATSA_SUBQUERY;  
  2.   
  3. USE VATSA_SUBQUERY;  
  4.   
  5. CREATE TABLE Library(  
  6.   BookNumber int NOT NULL,  
  7.   BookCode varchar(15) NOT NULL,  
  8.   BookIssue int NOT NULL,  
  9.   CostEach decimal(10, 2) NOT NULL,  
  10.   PRIMARY KEY(BookNumber, BookCode)  
  11. );  
  12.   
  13. CREATE TABLE Bookorder(  
  14.   BookNumber int NOT NULL,  
  15.   orderDate date NOT NULL,  
  16.   shippedDate date DEFAULT NULL,  
  17.   Status varchar(50),  
  18.   PRIMARY KEY(BookNumber)  
  19. ); 
There are 3 types of sub-queries in SQL, lets us see.
 
A)  Predicate Sub-queries
It extended logical constructs in the WHERE (and HAVING) clause.
 
B)  Scalar Sub-queries
In the standalone queries that return a single value; they can be used anywhere a scalar value is used.
 
C)  Table Sub-queries
In this type of subqueries queries are nested in the FROM clause.
 
Note:
All sub queries must be enclosed in parentheses. 
 

A) PREDICATE SUBQUERIES

 
These subqueries can be used in the HAVING and WHERE clause only because both are a special logical construct. These subqueries must retrieve one column.
 

1)  MySQL subquery with IN and NOT IN operators (In WHERE clause)

 
a) IN Subquery
 
The IN subquery tests if a scalar values match with the single query column value in any subquery result row.
 
Syntax:  
Outer_Query IN (Inner_Query)
 
Example: With the help of the following example we are getting the list of shipped date and status that are available in library table.
  1. SELECT * FROM bookorder WHERE BookNumber IN(  
  2.   SELECT booknumber FROM library  

 
b) NOT IN Subquery
 
Syntax:  
Outer_Query NOT IN (Inner_Query)
 
Example: In this example we are getting the list of shippeddate and status that are not available in library table.
  1. SELECT * FROM bookorder WHERE BookNumber NOT IN(  
  2.   SELECT booknumber FROM library  

 

2)  Quantified Subqueries (MySQL Subquery with Comparison operators)

 
A quantified subquery can use the all comparison operators for several types of queries. You can use comparison operators like =, >, < to compare a single value returned by the subquery with the expression in the WHERE clause.
 
Syntax:
Outer_Query {= | > | < | > = | < = | < >} {ANY | ALL | SOME} (Inner_Query)
 
The comparison operator is used to compare Outer_Query to the single query column value from each subquery result row. If we are using ALL clause then must match the all rows in subquery, or subquery must be empty. If we are using ANY or SOME clause, then must match at least one row in the subquery.
 
Example:
  1. SELECT * FROM bookorder WHERE BookNumber = ANY(  
  2.   SELECT booknumber FROM library  

 

3)  MySQL Subquery with EXISTS and NOT EXISTS Subquery

 
a)   Subquery with EXISTS
 
The EXISTS subquery is used to tests whether a sub-query returns at least one row or a qualifying row exists.
 
Syntax:
Exists (Inner_Query)
 
Any EXISTS sub-query should contain an outer reference. It must be a correlated sub-query.
 
Example
  1. SELECT * FROM bookorder bo WHERE EXISTS(  
  2.   SELECT *  
  3.   FROM library l WHERE bo.BookNumber = l.BookNumber  

 
b)   Subquery with NOT EXISTS
 
The EXISTS subquery is used to tests whether a sub-query returns at least one row or a qualifying row exists.
 
Syntax:
NOT Exists (Inner_Query)
 
Any EXISTS subquery should contain an outer reference. It must be a correlated subquery.
 
Example
  1. SELECT * FROM bookorder bo WHERE NOT EXISTS(  
  2.   SELECT *  
  3.   FROM library l WHERE bo.BookNumber = l.BookNumber  

 

B) SCALAR SUBQUERIES

 
The Scalar Subquery is a subquery which returns a single value. A Scalar subquery can be used almost anywhere a single column value can be used. The subqueries have to reference only one column in the select list. It must not retrieve more than one row. When subquery retrieves one row then the value of select list column becomes the value of the Scalar Subquery.
 

C) Table Subqueries

 
These subqueries are used in the FROM Clause, replace the table name. These subqueries can have correlation name also.
 
Example
  1. SELECT bookorder.*, shippedDate, status  
  2. FROM bookorder, library  
  3. WHERE bookorder.BookNumber = library.BookNumber  
  4. AND Status = 'In progress' 
Using Single Value Subqueries
 
There are two types we have to use this command. Firstly we will start with a simple query:
  1. SELECT MAX(BI), MIN(BI), FLOOR(AVG(BI))   
  2. FROM(  
  3.   SELECT bookissue, count(bookissue) AS BI FROM library GROUP BY bookissue) AS totalissued 
 
In the above example we retrieve only a single value and it’s representing the maximum, minimum. In this example we used a MySQL Function MAX() ,MIN(), and AVG() that finds the greatest, smallest, and average values in a specified column.
 
Single value subqueries are used to return a single column value and then they are typically used for comparison.
 
For Example
  1. SELECT * FROM bookorder bo, library l  
  2. WHERE bo.BookNumber = l.BookNumber  
  3. AND l.BookIssue = (  
  4.   SELECT MAX(BookIssue) FROM library) 
 
Reference
https://www.mysqltutorial.org/
 

CONCLUSION

 
In this article, I have discussed the concept of MySQL Subquery with various examples.
 
I hope you enjoyed this article. Follow C# Corner to learn more new and amazing things about MySQL. 
 
Thanks for reading this article!