Subqueries in MySQL

Introduction

In this tutorial, I am going to explain 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 the inner query, and the query that contains the subquery (inner query) is known as the 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 a few tables with dummy data, which I have attached here also.

CREATE DATABASE VATSA_SUBQUERY;  
  USE VATSA_SUBQUERY;  
  CREATE TABLE Library(  
  BookNumber int NOT NULL,  
  BookCode varchar(15) NOT NULL,  
  BookIssue int NOT NULL,  
  CostEach decimal(10, 2) NOT NULL,  
  PRIMARY KEY(BookNumber, BookCode)  
);  
  CREATE TABLE Bookorder(  
  BookNumber int NOT NULL,  
  orderDate date NOT NULL,  
  shippedDate date DEFAULT NULL,  
  Status varchar(50),  
  PRIMARY KEY(BookNumber)  
); 

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 clauses 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 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 shipping dates and statuses that are available in the library table.

SELECT * FROM bookorder WHERE BookNumber IN(  
  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 shipping dates and statuses that are not available in the library table.

SELECT * FROM bookorder WHERE BookNumber NOT IN(  
  SELECT booknumber FROM library  
) 

2. Quantified Subqueries (MySQL Subquery with Comparison operators)

A quantified subquery can use 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 the ALL clause, then must match all rows in a subquery, or the subquery must be empty. If we are using ANY or SOME clause, then must match at least one row in the subquery.

Example

SELECT * FROM bookorder WHERE BookNumber = ANY(  
  SELECT booknumber FROM library  
) 

3. MySQL Subquery with EXISTS and NOT EXISTS Subquery

a. Subquery with EXISTS

The EXISTS subquery is used to test 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

SELECT * FROM bookorder bo WHERE EXISTS(  
  SELECT *  
  FROM library l WHERE bo.BookNumber = l.BookNumber  
) 

b. Subquery with NOT EXISTS

The EXISTS subquery is used to test 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

SELECT * FROM bookorder bo WHERE NOT EXISTS(  
  SELECT *  
  FROM library l WHERE bo.BookNumber = l.BookNumber  
) 

B. SCALAR SUBQUERIES

The Scalar Subquery is a subquery that 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 the subquery retrieves one row, then the value of the select list column becomes the value of the Scalar Subquery.

C.Table Subqueries

These subqueries are used in the FROM Clause to replace the table name. These subqueries can have correlation names also.

Example

SELECT bookorder.*, shippedDate, status  
FROM bookorder, library  
WHERE bookorder.BookNumber = library.BookNumber  
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:

SELECT MAX(BI), MIN(BI), FLOOR(AVG(BI))   
FROM(  
  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

SELECT * FROM bookorder bo, library l  
WHERE bo.BookNumber = l.BookNumber  
AND l.BookIssue = (  
  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!