If you’ve already mastered SQL SELECT queries with WHERE, GROUP BY, or HAVING, the next thing to learn is SQL subqueries.
Below you will find the tutorial divided into three parts:
- Answer to what are subqueries.
- Explanation of how subqueries are used in SQL.
- Several examples of subqueries, from easiest to most complex.
- What is a SQL subquery?
- How do you use a SQL subquery?
- Example SELECT query
- Add the WHERE clause
- subquery in parentheses
- Examples of subqueries in SQL
- Example subqueries 1
- Example of subqueries 2
- Nested query example 3
- Example 4 subquery with nested subquery
- SQL Subquery Conclusion
What is a SQL subquery?
First a definition: A subquery is an SQL SELECT statement nested within another SELECT, INSERT, DELETE, or UPDATE statement. Also, you should know that they will almost always be done inside the WHERE clause of the queries.
Another way to explain it would be to say that we have a SELECT inside another SELECT, INSERT … INTO, UPDATE or DELETE query.
There are also three different ways to create a subquery:
- With ANY, ALL, SOME preceding the nested query.
- Making use of the IN or NOT IN expression before the subquery.
- EXISTS or NOT EXISTS and then the subquery.
How do you use a SQL subquery?
In this part I am going to focus on nested queries inside the WHERE clause, since I explained INSERT INTO queries in another article.
To make a SQL query with subqueries we will need:
- A normally formed query of type SELECT, UPDATE, or DELETE
- A WHERE or HAVING clause with the desired conditions plus one of the above SQL operators (ANY, ALL,SOME,IN,NOT IN, EXISTS, or NOT EXISTS) or comparison operators such as “=”, “>” or “<"
- The SELECT query between parentheses.
There are several important points about its use to take into account to avoid errors:
- The subquery will always be enclosed in parentheses, with no exceptions.
- Subqueries can be nested within other subqueries.
- The subquery must always return the same type and number of values to compare: there cannot be a subquery that returns two columns for a comparison with 1 column.
- The subquery should always (but is not required) to be placed to the right of the relational operator.
Now that you have the theory going through your head, I will show you a small example following the three points just mentioned.
Example SELECT query
First I am going to create a normal SELECT query, which gets the first and last names of the rows of a table called citizens
SELECT nombre, apellidos FROM ciudadanos
Add the WHERE clause
Now I add the WHERE clause to the above query and I’m going to add one of the supported operators to it.
SELECT nombre, apellidos FROM ciudadanos WHERE dni IN ....
subquery in parentheses
And finally I am going to make the subquery between parentheses that selects the DNI column of a “delinquents” table:
SELECT nombre, apellidos FROM ciudadanos WHERE dni IN ( SELECT dni FROM delincuentes );
That’s it! What will happen now is that the database will do:
- First solve the subquery.
- Then it will compare the values returned by the ID of the subquery with the ID of the SELECT “FATHER” query.
- It will return the values of the main query as the result of the query.
Examples of subqueries in SQL
Time for examples. From here I am going to show you several examples so that you finish internalizing the subqueries.
I will start with a couple of simple examples, following the trend of the previous section, and I will continue with more complex and advanced examples.
For the following examples I will create 2 tables:
- table about video game the one i will call videogame.
- table about players the one i will call player.
The CREATE SQL queries are as follows:
CREATE TABLE videojuego ( id INT (11) NOT NULL AUTO_INCREMENT PRIMARY KEY, titulo VARCHAR(100) NOT NULL, lenguaje VARCHAR(5) NOT NULL, resumen TEXT NULL, nota DECIMAL (3,1), total_jugadores INT DEFAULT 0 ); CREATE TABLE jugador( id INT (11) NOT NULL AUTO_INCREMENT, nombre VARCHAR(80) NOT NULL, edad INT NOT NULL, es_famoso INT NOT NULL DEFAULT 0, id_videojuego INT NOT NULL, FOREIGN KEY(id_videojuego) REFERENCES videojuego(id), fecha_registro DATE NULL );
Now that I have my two tables, we are going to perform some subqueries.
Example subqueries 1
Retrieve all games where at least 1 player plays:
SELECT * FROM videojuego WHERE id IN ( SELECT id_videojuego FROM jugador );
Another alternative way to do this subquery would be using the ANY or SOME clause:
SELECT * FROM videojuego WHERE id = ANY ( SELECT id_videojuego FROM jugador ); //alternativa con SOME SELECT * FROM videojuego WHERE id = SOME ( SELECT id_videojuego FROM jugador );
Actually the two conditions are the same since SOME is an ALIAS of ANY, which means that they are the same command.
Example of subqueries 2
In the following example I will select all the players who play a game in Spanish:
SELECT nombre, es_famoso FROM jugador WHERE id_videojuego IN ( SELECT id FROM videojuego WHERE lenguaje = 'es' );
Nested query example 3
Now I’m going to make another query with a subquery and aggregate functions, specifically using MAX.
To prove it to you, I select the players who are the oldest among the players:
SELECT nombre, apellidos FROM jugador WHERE edad = ( SELECT MAX(edad) FROM jugador );
Example 4 subquery with nested subquery
In this example I am going to increase the difficulty a bit, I am going to show you the almost infinite possibilities of subqueries using a subquery nested within another subquery. You will understand it better with the example.
Remember: Subqueries are resolved before your main query
SELECT * FROM videojuego WHERE lenguaje IN ( SELECT lenguaje FROM videojuego WHERE id = SOME ( SELECT id_videojuego FROM jugador WHERE es_famoso = 1 ) );
You’ve understood? The query in its entirety selects all the video games of one of the languages that are played by some famous gamer.
In order of execution of the subqueries (from the last to the main one) I select:
- The ID of all famous players
- The language of all video games that if ID matches the ID of famous players.
- All the data of the videogames whose language matches one of the languages of the subquery of point 2.
#5 Subquery example with UPDATE SQL
I think that with SELECT queries you can see all the possibilities of subqueries, but so that you can see an example of how to use them in UPDATE or DELETE graphically, I’ll leave you an example now.
Specifically, I am going to add 1 to the total_players counter of those video games that have a player playing it on the date ‘2019-12-01’
UPDATE videojuego SET total_jugadores = jugadores + 1 WHERE id IN ( SELECT id_videojuego FROM jugador WHERE fecha = '2019-12-01' )
SQL Subquery Conclusion
Well, I didn’t want to wind up more, I’ll continue with the subqueries if necessary in another article.
I hope it has been useful to you! If so, it would be great if you left a comment with any questions/contributions to improve the article together. Also, if you share it, you will help us stay motivated and create useful content for programmers around the world.