SQL Subquery – Nested Query in SQL

1. Introduction

In this article, we will look at many examples of how to use SQL subquery using MySQL RDBMS.

2. What is the SQL Subquery?

A subquery is also called a Nested Query. A Subquery is a Select Query inside another query. Subqueries can be used inside other select, insert, update and delete commands. It can also be used in the FROM clause, with operators like <,> =, etc., with the where and having clauses. Subqueries are used on a very large scale to nest multiple tables and conditions inside each other and to create very complex queries.    

3. Restrictions on Subqueries

There are some things Mysql does not allow in subqueries. The restrictions are as follows:

• We cannot modify and select from the same table in a subquery. This applies to Insert, Update, and Delete.
• Only some of the Row comparison operators Like IN and Not IN can be used in a subquery.
• Subqueries used with the IN and Not IN clause cannot have the LIMIT clause in the subquery.

These restrictions are mainly due to the way the mysql optimizes and implements them.

4. Syntax

The Basic syntax of a Subquery is as follows:

SELECT column_list (s) FROM  table_name  
WHERE  column_name OPERATOR  
   (SELECT column_list (s)  FROM table_name [WHERE conditions]) 

5. Setup for examples of Subqueries

Forgoing through the examples related to the Intersect operator, we will consider the database called “Sakila”. This is an example database given by MySQL. The schema structure is available on the documentation page. For running the queries, we will use the MySQL Workbench. The documentation for MySQL Workbench is available here.

6. Examples for Subqueries.

We will see many examples of subqueries in mysql.

6.1 Sub query as a scalar operand

A subquery can be used as a scalar operand to get a single value out. This is done in the select part of the Query. As an example

SELECT (SELECT CITY FROM CITY WHERE CITY_ID=2) FROM ADDRESS WHERE CITY_ID=2;

6.2 Subqueries with Operands

Subqueries can be used with operators like <,>,<=,>=,= etc. below are 2 examples of the same.

SELECT F.TITLE
	FROM FILM AS F
	WHERE F.LANGUAGE_ID = (SELECT LANGUAGE_ID FROM LANGUAGE WHERE NAME = 'ENGLISH')
	AND F.TITLE LIKE 'I%' OR 'L%' ;

Second example

SELECT TITLE,RENTAL_RATE,REPLACEMENT_COST,RATING FROM FILM 
WHERE REPLACEMENT_COST >= (SELECT AVG(REPLACEMENT_COST) FROM FILM); 

6.3 Sub queries with In and Not In operators

Examples of subqueries with the In and Not In operators are as follows:

6.3.1 Query with IN

The In operator can be used both inside and outside the subqueries. We cannot use the LIMIT clause inside the subquery when we are using the IN operator.

SELECT F.TITLE,F.DESCRIPTION,F.LENGTH,F.RATING,GROUP_CONCAT(CONCAT(A.FIRST_NAME,A.LAST_NAME) SEPARATOR ', ') AS `ACTORS`
FROM FILM_ACTOR FA, FILM F ,ACTOR A
WHERE F.FILM_ID = FA.FILM_ID 
AND A.ACTOR_ID = FA.ACTOR_ID
AND F.FILM_ID IN (
    SELECT FILM_ID FROM FILM_CATEGORY, CATEGORY
    WHERE CATEGORY.CATEGORY_ID = FILM_CATEGORY.CATEGORY_ID
    AND CATEGORY.CATEGORY_ID = 6)
GROUP BY F.FILM_ID;

6.3.2 Not In operator

The same rules as the IN operator apply for the Not IN operator.

SELECT * FROM ACTOR
WHERE ACTOR_ID NOT IN 
	(SELECT ACTOR_ID FROM FILM_ACTOR
	 WHERE FILM_ID IN (506,605,939))
ORDER BY FIRST_NAME;

6.4 Nested Subqueries

Subqueries can be nested one or more times. However, too much nesting is not recommended as it may lead to performance issues especially with huge datasets. In the case of multiple nested subqueries, MySQL prefers joins instead since they are better optimized.

SELECT CONCAT(FIRST_NAME,' ',LAST_NAME) AS 'ACTORS'
FROM ACTOR
WHERE ACTOR_ID IN 
	(SELECT ACTOR_ID FROM FILM_ACTOR WHERE FILM_ID = 
            (SELECT FILM_ID FROM FILM WHERE TITLE = 'BREAKFAST GOLDFINGER'));

Multiple nesting

SELECT SUM(AMOUNT) AS "TOTAL SALES"
FROM PAYMENT
WHERE RENTAL_ID IN (SELECT RENTAL_ID FROM RENTAL
       WHERE INVENTORY_ID IN 
            (SELECT INVENTORY_ID FROM INVENTORY
             WHERE FILM_ID IN 
                   (SELECT FILM_ID FROM FILM
                    WHERE FILM_ID IN 
                          (SELECT FILM_ID FROM FILM_CATEGORY
                            WHERE CATEGORY_ID IN 
                                  (SELECT CATEGORY_ID FROM CATEGORY)))));

6.5 Subqueries exists or not exists

The Exists and not Exists operators also use subqueries.

Exists example

SELECT C.FIRST_NAME,C.LAST_NAME,A.ADDRESS,A.POSTAL_CODE
FROM CUSTOMER C,ADDRESS A
WHERE C.ACTIVE =1 
AND C.ADDRESS_ID = A.ADDRESS_ID
AND EXISTS (SELECT * 
            FROM CITY CT
            WHERE  CT.COUNTRY_ID IN (8,19,24,169) 
            AND CT.CITY_ID = A.CITY_ID);

Not Exists example

SELECT * FROM INVENTORY WHERE
NOT EXISTS (
 SELECT 1 FROM RENTAL where RENTAL.INVENTORY_ID =10
 AND RETURN_DATE IS NULL
)
LIMIT 20;

6.6 Subqueries with Having

Just as we can use subqueries in the Where clause, we can also use subqueries with the Having clause as well.

SELECT * FROM FILM
WHERE FILM_ID NOT IN(
SELECT FILM_ID
FROM FILM JOIN FILM_CATEGORY USING (FILM_ID) JOIN CATEGORY USING (CATEGORY_ID)
GROUP BY CATEGORY.NAME
HAVING AVG(LENGTH) >= (SELECT AVG(LENGTH) FROM FILM))
AND FILM_ID NOT IN(
SELECT FILM_ID FROM INVENTORY
WHERE INVENTORY_ID IN (SELECT INVENTORY_ID FROM RENTAL WHERE INVENTORY_ID IS NULL));

6.7 Derived tables using subqueries

WE can use a subqueries to create a temporary table. This table is used in the From clause.

SELECT AVG(AVERAGES) FROM 
	(SELECT 
		CUSTOMER_ID,
		SUM(AMOUNT) AVERAGES
	FROM PAYMENT
	GROUP BY CUSTOMER_ID) AS TOTALS;

6.8 Subqueries in Update, delete and insert

We can use subqueries to Update , insert and delete records from the schema. For these examples, we have created a new table called Cust_payments

CREATE TABLE CUST_PAYMENTS(
    CUSTOMER_ID SMALLINT UNSIGNED,
    FULLNAME VARCHAR(50),
    AMOUNT INT,
    NUMBER_OF_PAYMENTS INT
);

Insertion Query

INSERT INTO CUST_PAYMENTS 
SELECT CUSTOMER_ID, CONCAT(FIRST_NAME, " ", LAST_NAME) AS NAME, AMOUNT, COUNT(AMOUNT) 
FROM CUSTOMER 
JOIN PAYMENT P1 USING(CUSTOMER_ID) 
WHERE AMOUNT > 
      (SELECT AVG(AMOUNT) FROM PAYMENT P2 WHERE P2.CUSTOMER_ID=P1.CUSTOMER_ID) 
GROUP BY CUSTOMER_ID;

Update Query

UPDATE ADDRESS
       SET DISTRICT = 'BATCAVE'
       WHERE CITY_ID IN (SELECT CITY_ID FROM CITY WHERE CITY = 'BATMAN' );

6.9 Sub queries with errors

We cannot use the same table in the subquery if we are trying to insert/update/delete from it. The below 2 examples show the error Mysql throws when this occurs

DELETE FROM CUST_PAYMENTS WHERE CUSTOMER_ID IN (
SELECT CUSTOMER_ID FROM CUST_PAYMENTS WHERE NUMBER_OF_PAYMENTS <=5);

UPDATE  CUST_PAYMENTS 
SET AMOUNT = 13 
WHERE CUSTOMER_ID IN 
       (SELECT CUSTOMER_ID FROM CUST_PAYMENTS WHERE AMOUNT =11);

7. Summary

In the article, we saw examples related to the Subqueries in MySQL. We also saw the restrictions that Mysql places on users and the errors that result. More details about subqueries and how MySQL optimizes them is available here.

8. Download the Source Code

This was an example of the SQL Subquery – Nested Query in SQL using MySQL RDBMS.

Last updated on Feb. 27th, 2022

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

and many more ....

I agree to the Terms and Privacy Policy

Sign up

Thank you!

We will contact you soon.