Home DB How to write nested queries in MySQL

How to write nested queries in MySQL

6
How to write nested queries in MySQL

This article discusses nested queries that result in a single value.

Nested Queries

In this article, we’ll examine nested queries, a query within another query. With the ability to nest a query, we can combine queries to get the desired result in a single uber query rather than executing each constituent query individually. Nested queries are generally slower but more readable and expressive than equivalent join queries. Also, in some situations nested queries are the only way to retrieve desired information from a database.

-- The article queries are reproduced below for convenient copy/paste into the terminal.
-- Query 1
SELECT URL AS "Brad's Insta Page" FROM Actors INNER JOIN DigitalAssets WHERE AssetType = "Instagram" AND FirstName
-- Query 2
SELECT URL FROM DigitalAssets WHERE AssetType = "Instagram" AND ActorId = (SELECT Id FROM Actors WHERE FirstName = "Brad");
-- Query 3
SELECT FirstName ="Brad" FROM Actors INNER JOIN DigitalAssets ON ActorId = Id WHERE LastUpdatedOn = (SELECT MAX(LastUpdatedOn) FROM DigitalAssets);

SELECT column1 AS “Brad’s Insta Page” FROM table1 INNER JOIN table2 WHERE column2 = “Instagram” AND column3 =”Brad”;

Many times, inner join queries can be rewritten as nested queries.For instance, let’s say we want to find the Instagram page for BradPitt. One way to glean this information is to use the following innerjoin query:

SELECT URL AS "Brad's Insta Page" FROM Actors INNER JOIN DigitalAssets WHERE AssetType = "Instagram" AND FirstName ="Brad";
How to write nested queries in MySQL

The above join query can be rewritten as a nested query as follows:

SELECT URL FROM DigitalAssets WHERE AssetType = "Instagram" ANDActorId = (SELECT Id FROM Actors WHERE FirstName = "Brad");

The nested query is enclosed within parentheses. The result of the nested query is a single integer value that feeds into the WHERE condition of the outer query. Another alternative is to first execute the inner query and remember the output it produces, then execute the outer query and plug in the output of the inner query in the WHERE clause. When a subquery returns a single value, it is said to return a scalar operand. Nesting allows us to get the result in one shot. In the example above, if we change the sub-query to return multiple values or return the entire row, the overall query will fail as shown below:

SELECT column1 FROM table1 INNER JOIN table2 ON column2 = Id WHERE column3 = (SELECT MAX(column3) FROM table2);

Let’s work with another example, say we are asked to return the actor who has most recently updated any of his or her online social accounts. Given the schema of our database, we know the column LastUpdatedOn stores the timestamp of when an actor last performed an activity on their online account. We could use the MAX function to find the row with the most recent activity. Next, we’ll need to match the actor ID with the maximum value for the LastUpdatedOn column with a row with the same ID in the Actors table. This query can be written as an inner join query as follows:

SELECT FirstName FROM Actors INNER JOIN DigitalAssets ON ActorId = Id WHERE LastUpdatedOn = (SELECT MAX(LastUpdatedOn) FROM DigitalAssets);

Note that if there is more than one actor with the most recent activity, the above query will return more than one row. This query demonstrates a scenario where we don’t have an alternative other than a nested query to get our desired information in single query execution.

Nested Column Queries

We’ll see nested queries that return values belonging to the same column.

-- Query 1
SELECT * FROM Actors INNER JOIN DigitalAssets ON ActorId=Id WHERE AssetType = ANY (SELECT DISTINCT AssetType FROM DigitalAssets WHERE AssetType != 'Website');

-- Query 2
SELECT * FROM Actors INNER JOIN DigitalAssets ON ActorId=Id WHERE AssetType != 'Website';

-- Query 3
SELECT FirstName, SecondName FROM Actors WHERE Id = ANY (SELECT ActorId FROM DigitalAssets WHERE AssetType = 'Facebook');

-- Query 4
SELECT FirstName, SecondNameFROM Actors WHERE Id IN (SELECT ActorId FROM DigitalAssets WHERE AssetType = 'Facebook');

-- Query 5
SELECT FirstName, SecondName FROM Actors WHERE NetworthInMillions > ALL (SELECT NetworthInMillions FROM Actors WHERE FirstName LIKE "j%");

SELECT * FROM table1 INNER JOIN table2 ON column1=Id WHERE column2 = ANY (SELECT DISTINCT column2 FROM table2 WHERE column2 != ‘Website’);

We’ll use a slightly contrived example this time. Imagine we want to list all the social media accounts for all the actors, except for their personal websites. From our database schema we know that the table DigitalAssets has a column AssetType, which is essentially an enum and has a value website to denote an actor’s personal website. The DigitalAssets table by itself can’t give us the names of the actors since it only contains actor IDs. We’ll require an inner join with the Actors table to get the actor names. The complete query is shown below:

SELECT * FROM Actors INNER JOIN DigitalAssets ON ActorId=Id WHERE AssetType = ANY (SELECT DISTINCT AssetType FROM DigitalAssets WHERE AssetType != 'Website');

The subquery returns all the enum values for the column AssetTypeexcept the value “Website”. The WHERE clause of the outer query sets up a condition which evaluates to true whenever the column AssetType of the resulting inner join equals any of the values returned by the inner query. The ANY operator allows us to match the column AssetType with any one of the values returned for the column AssetType.
Granted, the same query can be written much simpler as follows without the need for an inner query, but the intention was to demonstrate a column subquery.

-- A much simpler approach to get the same result
SELECT * FROM Actors
INNER JOIN DigitalAssets ON ActorId=Id
WHERE AssetType != 'Website';

SELECT column1, column2 FROM table1 WHERE column3 = ANY (SELECT column4 FROM table2WHERE column5 Facebook );

Let’s work on another example. Say we now want to find the names of all the actors that have a Facebook presence. One way we can answer this query is to first collect all the actor IDs from the DigitalAssets table that have Facebook asset types. Next, we select all those rows from the Actors table whose ID matches any of the IDs from the first query:

SELECT FirstName, SecondName FROM Actors WHERE Id = ANY (SELECT ActorId FROM DigitalAssetsWHERE AssetType Facebook );

The ANY clause has an alias IN that can be used interchangeably. We can rewrite the above query as follows:

SELECT FirstName, SecondName FROM Actors WHERE Id IN (SELECT ActorId FROM DigitalAssets WHERE AssetType = 'Facebook');

SELECT column1, column2 FROM table1 WHERE column3 > ALL (SELECT column4 FROM table1 WHERE column1 LIKE “j%”);

The operator ANY, and its alias IN, match at least one value from a group of values. On the contrary, there’s another operator, ALL, that must match all the values in the group. As an example, imagine we are asked to find out the list of actors that have a net worth greater than all the actors whose first name starts with the letter ‘J’. The query would look as follows:

SELECT FirstName, SecondName FROM Actors WHERE NetworthInMillions > ALL (SELECT NetworthInMillions FROM Actors WHERE FirstName LIKE "j%");

Nested Row Queries

we’ll study nested queries that return rows, allowing the outer query to match on multiple different column values. Furthermore, so far, we have used nested queries only with the WHERE clause, but now we’ll also use them with the FROM clause.

-- Query 1
SELECT FirstName FROM Actors INNER JOIN DigitalAssets ON Id=ActorId AND MONTH(DoB) = MONTH(LastUpdatedOn) AND DAY(DoB) = DAY(LastUpdatedOn);

-- Query 2
SELECT FirstName FROM Actors WHERE (Id, MONTH(DoB), DAY(DoB)) IN ( SELECT ActorId, MONTH(LastUpdatedOn), DAY(LastUpdatedOn) FROM DigitalAssets);

--Query 3
SELECT ActorId, AssetType, LastUpdatedOn FROM DigitalAssets;

-- Query 4
SELECT FirstName, AssetType, LastUpdatedOn FROM Actors INNER JOIN (SELECT ActorId, AssetType, LastUpdatedOn FROM DigitalAssets) AS tbl ON ActorId = Id;

-- Query 5
SELECT FirstName, AssetType, LastUpdatedOn FROM Actors INNER JOIN (SELECT ActorId, AssetType, LastUpdatedOn FROM DigitalAssets) AS tbl ON ActorId = Id WHERE FirstName = "Kim";

-- Query 6
SELECT FirstName, AssetType, LastUpdatedOn FROM Actors INNER JOIN (SELECT ActorId, AssetType, LastUpdatedOn FROM DigitalAssets) AS tbl ON ActorId = Id WHERE FirstName = "Kim" ORDER BY LastUpdatedOn DESC LIMIT 1;

SELECT column1 FROM table1 INNER JOIN table2 ON Id=colu mn2 AND MONTH(column3) = MONTH(column4) AND DAY(column3) = DAY(column4);

Let’s say we want to find the list of all the actors whose latest update to any of their online accounts was on the day of their birthday. The date of birth for each actor is in the Actors table and the LastUpdatedOn column is in the DigitalAssets table. We can extract the birthday month and day using the MONTH() and DAY() functions on the DoB column and match them with the corresponding extracted values from the LastUpdatedOn column. Finally, we’ll also need to match the actor IDs in the two tables. The inner join query to get the results is as follows:

SELECT FirstName FROM Actors INNER JOIN DigitalAssets ON Id=ActorId AND MONTH(DoB) = MONTH(LastUpdatedOn) AND DAY(DoB) = DAY(LastUpdatedOn);

Instead of the inner join we can also use a nested query. We’ll return three columns from the inner query, the day and month of the last update and the actor ID. The outer query will match on these three columns using the IN clause.

SELECT FirstName FROM Actors WHERE (Id, MONTH(DoB), DAY(DoB)) IN ( SELECT ActorId, MONTH(LastUpdatedOn), DAY(LastUpdatedOn) FROM DigitalAssets);

The inner query returns a temporary result set of several rows with three columns. The outer query lists columns from the Actors table that should be matched against the columns from the result set of the inner query and the match takes place in the order of the listing of columns. The syntax allows us to match multiple columns per row for several rows. The first name column of the matching rows of the inner query’s result set are then returned as the result of the overall query.

SELECT column1, column2, column3 FROM table2;

To demonstrate using a nested query with the FROM clause, we’ll move onto a slightly harder query to answer. Say you are asked to find out which of her online accounts Kim Kardashian most recently updated. Let’s think about it for a minute: the two pieces of information we need are present in the two tables: Actors (name) and DigitalAssets (last update timestamp). First, let’s understand how we can find the latest updated account for an actor. If we know the ActorID, we can use the following query to list all the online accounts belonging to that actor along with their latest update times.

SELECT ActorID, AssetType, LastUpdatesOn FROM DigitalAssets;

The above query also retrieves us the actor IDs. If we could determine Kardashian’s actor ID from the output of the above query, we could use that in a WHERE clause and answer the original question, but we don’t. We’ll need to join the result of the above query with the actor table based on actor IDs to know which rows from the DigitalAssets table belong to Kardashian. So far, we have:

SELECT FirstName, AssetType, LastUpdatedOn FROM Actors INNER JOIN (SELECT ActorId, AssetType, LastUpdatedOn FROM DigitalAssets) AS tbl ON ActorId = Id;

Note that we give an alias of tbl to the result set of the inner query. When the result of an inner query is used as a derived table, MySQL requires us to provide an alias for the table. This is a syntax requirement. If we skip aliasing the result set of the inner query, we’ll not be able to use it in the join clause. In order to narrow down the rows for Kardashian we’ll need to add a WHERE clause with the condition FirstName=”Kim”. Now we’ll have all the digital accountsbelonging to Kardashian as follows:

SELECT FirstName, AssetType, LastUpdatedOn FROM Actors INNER JOIN (SELECT ActorId, AssetType, LastUpdatedOn FROM DigitalAssets) AS tbl ON ActorId = Id WHERE FirstName = "Kim";

The last piece is to order the rows by LastUpdatedOn to get the latest updated online account for Kardashian.

SELECT FirstName, AssetType, LastUpdatedOn FROM Actors INNER JOIN (SELECT ActorId, AssetType, LastUpdatedOn FROM DigitalAssets) AS tbl ON ActorId = Id WHERE FirstName = "Kim"ORDER BY LastUpdatedOn DESC LIMIT 1;

The astute reader would realize that in the FROM clause we could have just as well used the DigitalAssets table instead of plugging in a nested query. Sure, we could, but the intent here is to demonstrate how nested queries can be used with the FROM clause, so in that sense, it is a slightly contrived example.

EXISTS Operator

we’ll learn about the boolean operator EXISTS and its complement, NOT EXISTS. The EXISTS operator is usually used to test if a subquery returns any rows or none at all.

-- The below queries are reproduced for convenient copy/paste into the terminal.
-- Query 1
SELECT * FROM Actors WHERE EXISTS ( SELECT * FROM DigitalAssets WHERE BINARY URL LIKE "%clooney%");
-- Query 2
SELECT * FROM Actors WHERE NOT EXISTS ( SELECT * FROM DigitalAssets WHERE BINARY URL LIKE "%clooney%");

SELECT * FROM table1 WHERE EXISTS ( SELECT * FROM table2 WHERE BINARY column1 LIKE “%clooney%”)

Let’s start with a simple example. We’ll check if our table DigitalAssets has any account owned by the actor George Clooney. If yes, we print the list of all the actors from our Actors table. Granted, the query doesn’t make much sense but bear with me as we’ll see more useful applications of the EXISTS operator in later lessons. The query is shown below:

SELECT * FROM Actors WHERE EXISTS ( SELECT * FROM DigitalAssets WHERE BINARY URL LIKE "%clooney%") 

The outcome is an empty set because we don’t have any entry for George Clooney in our DigitalAssets table. The subquery selects all the columns, but what the subquery selects is irrelevant because the EXISTS operator only checks for the existence of any rows from the result returned by the subquery. The WHERE condition of the outer query becomes false and the overall query returns an empty result set too.
Note we have used the BINARY operator to make the comparison case-insensitive which is not required if the collation is already set to be case-insensitive for the database.

SELECT * FROM table1 WHERE NOT EXISTS ( SELECT * FROM table2 WHERE BINARY URL LIKE “%clooney%”);

We add the NOT operator to the EXISTS clause and see the entireActors table print out.

SELECT * FROM Actors WHERE NOT EXISTS ( SELECT * FROM DigitalAssets WHERE BINARY URL LIKE "%clooney%");

Correlated Queries

Correlated queries are a type of nested queries. The distinguishing feature about them is that the inner query references a table or a column from the outer query.

-- Query 1
SELECT FirstName FROM Actors INNER JOIN DigitalAssets ON Id = ActorId WHERE URL LIKE CONCAT("%",FirstName,"%") AND AssetType="Twitter";

-- Query 2
SELECT FirstName FROM Actors WHERE EXISTS (SELECT URL FROM DigitalAssets WHERE URL LIKE CONCAT("%",FirstName,"%") AND AssetType="Twitter");

SELECT FirstName FROM Actors INNER JOIN DigitalAssets ON Id = ActorId WHERE URL LIKE CONCAT(“%”,FirstName,”%”) AND AssetType=”Twitter”;

Let’s say we want to know which actors have their first name as part of their Twitter handle. We can use an inner join query as follows:

SELECT FirstName FROM Actors INNER JOIN DigitalAssets ON Id = ActorId WHERE URL LIKE CONCAT("%",FirstName,"%") AND AssetType="Twitter";

An alternative is to write a correlated query to glean the same information from the database. Let’s see what the query looks like:

SELECT FirstName FROM Actors WHERE EXISTS (SELECT URL FROM DigitalAssets WHERE URL LIKE CONCAT("%",FirstName,"%") AND AssetType="Twitter");

The inner query references the column FirstName in its WHERE clause even though FirstName is part of the Actors table which is referenced only in the outer query. It is legal to access a table or any of its columns referenced in the outer query inside a sub-query. The value of FirstName for each row in the Actors table is provided as a scalar value to the inner query.
Pay attention to how we have used the EXISTS operator. We want to check for a condition that is true about each actor. We aren’t interested in what the inner query returns for each actor, rather only if it returns anything or not. If no result is returned for an actor, then the outer query for that actor evaluates to false and the name isn’t printed on the console.

Some FAQ’s for Nested query

What is a nested query in MySQL?

With the ability to nest a query, we can combine queries to get the desired result in a single uber query rather than executing each constituent query individually. Nested queries are generally slower but more readable and expressive than equivalent join queries. Also, in some situations nested queries are the only way to retrieve desired information from a database.

How do I create a nested query in MySQL?

We’ll use a slightly contrived example this time. Imagine we want to list all the social media accounts for all the actors, except for their personal websites. From our database schema we know that the table DigitalAssets has a column AssetType, which is essentially an enum and has a value website to denote an actor’s personal website. The DigitalAssets table by itself can’t give us the names of the actors since it only contains actor IDs. We’ll require an inner join with the Actors table to get the actor names.

6 COMMENTS

  1. Hey There. I found your weblog the use of msn. This is a very neatly written article. I will be sure to bookmark it and return to read more of your helpful information. Thanks for the post. I will definitely comeback. Devi Towney Garbers

LEAVE A REPLY

Please enter your comment!
Please enter your name here

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

Exit mobile version