sql

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;
sql Subquery as a scalar
Subquery as a scalar

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%' ;
sql Subquery using operators
Subquery using operators

Second example

SELECT TITLE,RENTAL_RATE,REPLACEMENT_COST,RATING FROM FILM 
WHERE REPLACEMENT_COST >= (SELECT AVG(REPLACEMENT_COST) FROM FILM); 
sql Subquery using operators
Subquery using operators

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;
sql Subquery using IN operator
Subquery using IN operator

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;
Subquery using  NOT IN operator
Subquery using NOT IN operator

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'));
SQL Subquery - Single level nesting
Single level nesting

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)))));
SQL Subquery - Nesting subqueries multiple times
Nesting subqueries multiple times

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);
sql Subquery with Exists
Subquery with Exists

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;
Subquery with Not Exists
Subquery with Not exists

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));
Subquery in the Having clause
Subquery in the Having clause

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;
subquery as a derived table
subquery as a derived table

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;
Insert using subqueries
Insert using subqueries

Update Query

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

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

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.

Download
You can download the full source code of this example here: SQL Subquery – Nested Query in SQL

Last updated on Feb. 27th, 2022

Reshma Sathe

I am a recent Master of Computer Science degree graduate from the University Of Illinois at Urbana-Champaign.I have previously worked as a Software Engineer with projects ranging from production support to programming and software engineering.I am currently working on self-driven projects in Java, Python and Angular and also exploring other frontend and backend technologies.
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Inline Feedbacks
View all comments
Back to top button