MySQL Subquery
A subquery is also called Nested Query. It is a Select Query inside another query. They can be used inside other select, insert, update and delete commands. We can also use them in the FROM clause, with operators like <,> =, etc., with the where and having clauses. Subqueries are used on a large scale to nest multiple tables and conditions inside each other and create complex queries.
There are some restrictions that MySQL puts on Subqueries. They are as follows:
- We cannot modify and select from the same table in a Select, Insert, Update and Delete subquery.
- We can use only some row comparisons like Like, Not in, etc., in Subqueries.
- We cannot use the LIMIT Clause with IN and Not IN subqueries.
MySQL Subquery syntax
The basic syntax for a subquery is:
SELECT column_list (s) FROM table_name
WHERE column_name OPERATOR
(SELECT column_list (s) FROM table_name [WHERE])
Subquery as a scalar operand in MySQL
A subquery can be used as a scalar operand to get a single value out. The subquery 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;
Subqueries with operands in MySQL
Subqueries can be used with operators like <,>,<=,>=,=, etc. below are two examples.
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%' ;
Example 2:
SELECT TITLE,RENTAL_RATE,REPLACEMENT_COST,RATING FROM FILM
WHERE REPLACEMENT_COST >= (SELECT AVG(REPLACEMENT_COST) FROM FILM);
Subqueries with 'In' and 'Not In' operators in MySQL
Examples of subqueries with the In and Not In operators are as follows:
Query with IN Operator:
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;
Using Not In Operator with subquery:
The same rules as the IN operator applies 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;
Nested subqueries in MySQL
Subqueries can be nested one or more times. However, too much nesting is not recommended as it may lead to performance issues, especially with massive datasets. In 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 with subquery:
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)))));
Subqueries with exists or not exists in MySQL
The Exists and not Exists operators also use subqueries.
Example with Exists:
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);
Example with Not Exists:
SELECT * FROM INVENTORY WHERE
NOT EXISTS (
SELECT 1 FROM RENTAL where RENTAL.INVENTORY_ID =10
AND RETURN_DATE IS NULL
)
LIMIT 20;
Subqueries with 'Having' clause in MySQL
Just as we can use subqueries in the Where clause, we can also use subqueries with the Having clause.
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));
Derived tables using subqueries in MySQL
WE can use a subquery 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;
Subqueries in the update, delete, and insert in MySQL
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.
Insertion Query:
Query to create a table,
CREATE TABLE CUST_PAYMENTS(
CUSTOMER_ID SMALLINT UNSIGNED,
FULLNAME VARCHAR(50),
AMOUNT INT,
NUMBER_OF_PAYMENTS INT
);
Query to insert table values with Subqueries,
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 Statement with a subquery:
Query to update table values with Subquery,
UPDATE ADDRESS
SET DISTRICT = 'BATCAVE'
WHERE CITY_ID IN (SELECT CITY_ID FROM CITY WHERE CITY = 'BATMAN' );
Delete Statement with a subquery:
Query to delete records from a table using a subquery,
DELETE FROM EMPLOYEES2
WHERE EMP_ID IN (SELECT EMP_ID FROM EMPLOYEES);
Subqueries with errors in MySQL
We cannot use the same table in the subquery if we are trying to insert/update/delete from it. The below two 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);
Error in update statement,
UPDATE CUST_PAYMENTS
SET AMOUNT = 13
WHERE CUSTOMER_ID IN
(SELECT CUSTOMER_ID FROM CUST_PAYMENTS WHERE AMOUNT =11);
