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.
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%' ;
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'));
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.
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 );
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 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);
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.
You can download the full source code of this example here: SQL Subquery – Nested Query in SQL