Back to articles list May 14, 2020 - 9 minutes read Beginner’s Guide to the SQL Subquery Ignacio L. Bisso Ignacio is a database consultant from Buenos Aires, Argentina. He’s worked for 15 years as a database consultant for IT companies like Informix and IBM. These days, he teaches databases at Sarmiento University and works as a PostgreSQL independent SQL consultant. A proud father of four kids with 54 years in his backpack, Ignacio plays soccer every Saturday afternoon, enjoying every match as if it’s his last one. Tags: sql learn sql subquery Subqueries are a powerful SQL resource, allowing us to combine data from multiple tables in a single query. In this article, we’ll teach you everything you need to begin using subqueries. Perhaps the simplest definition of a SQL subquery is “A query inside a query”. Subqueries are so easy to understand that they often appear in the opening chapters of SQL courses. However, there are many variants of subqueries that need to be explained. And although subqueries are generally used in the WHERE clause, you can use them in other clauses, such as FROM, HAVING, and SELECT. In short, there’s a lot more to know about subqueries than just what they are and where they go. So, let’s start with our first example of a SQL subquery for beginners. Basic Subqueries by Example Before going into subqueries, we need to explain our database tables. To relax our minds in this time of social distancing, I’ll use examples related to beautiful and relaxing places. Our sample database will have two tables. The first table is called best_10_places and it stores the 10 best places for different kinds of activities (like snorkeling, skiing, and trekking). The table has columns for the place name, the activity we can do there, the ranking of this place, and the closest city. Have a look: Place_NameActivityRanking_PositionClosest_City Praia do Sepulturasnorkeling1Florianopolis Hanauma Baysnorkeling2Honolulu Elliot Islandsnorkeling3Melbourne Cerro Catedralskiing1Bariloche Camino de Santiagotrekking1Compostela Cerro Ottotrekking2Bariloche Black Vulcanotrekking3Honolulu Table: best_10_places If you want to travel to any of these beautiful places, you’ll need a ticket; the one_way_ticket table has one record for any pair of cities that are connected by any kind of transportation. We will use this table to determine how to get from one city to another. The columns contain info on the origin city, destination city, ticket price, travel time, and transportation type (e.g. rail, air, etc.). Below is a subset of this table: City_OriginCity_DestinationTicket_PriceTravel_TimeTransportation ParisFlorianopolis830.0011hr 30 minair ParisHonolulu1564.0015hr 20 minair ParisMelbourne2200.0018hr 50minair ParisBariloche970.0012hr 20 minair MadridCompostela80.001hr 10minair Table: one_way_ticket Now we’re ready for the first example. Let’s suppose a person in Paris wants to go to the #1 place in the world for snorkeling. What type of transportation goes from Paris to this place? As you probably know, the simplest SQL query is formed by a SELECT, a FROM, and (optionally) a WHERE clause. And as we previously mentioned, a subquery is a query inside a query. So, in the next example you’ll see two queries: the main query (also called the outer query) and the subquery (in blue): SELECT city_destination, transportation, ticket_price, travel_time FROM one_way_ticket WHERE city_destination = ( SELECT closest_city FROM best_10_places WHERE activity_type = 'snorkeling' AND ranking_position = 1 ) AND city_origin = 'Paris' The subquery is executed first, returning the closest_city to the best snorkeling destination (the city of Florianopolis in Brazil). Then the main query is executed, replacing the subquery with its result (Florianopolis). The final result is: City_DestinationTransportationTicket_PriceTravel_Time Florianopolisair$ 830.0011hr 30 min When using subqueries: You must always enclose the subquery in parenthesis. Pay attention to the operator used to compare the subquery result. In our previous example, we used “=”; however, this operator should be used with subqueries that return only one row and only one column (also known as “scalar” subqueries). I suggest you read the article SQL Subqueries to see more beginner subquery examples explained in detaiI. Subqueries are also part of our SQL Basics course, a step-by-step tutorial that takes you through foundational SQL using examples and exercises. Scalar or Non-Scalar Subqueries: That Is the Question So, a scalar subquery returns only one column with only one row. What’s a non-scalar subquery? A subquery that returns multiple rows. There are many operators we can use to compare a column with a subquery. However, a few of them can only be used with scalar subqueries: =, >, >=, < and <=. If you use one of these operators, your subquery must be scalar. Let’s see an example with a scalar subquery. Suppose you have a customer who wants to go from Paris to Bariloche. Before buying the ticket, the customer wants to see if there are any places with a cheaper ticket. The query below will find those cities: SELECT city_destination, ticket_price, travel_time, transportation FROM one_way_ticket WHERE ticket_price < ( SELECT ticket_price FROM one_way_ticket WHERE city_destination = 'Bariloche' AND city_origin = 'Paris' ) AND city_origin = 'Paris' Again, the subquery is executed first; its result (the price of a Paris-Bariloche ticket, or $970) is compared with the column ticket_price in the outer query. This obtains all the records in one_way_ticket with a ticket_price value less than $970. The result of the query is shown below: City_DestinationTicket_PriceTravel_TimeTransportation Florianopolis830.0011hr 30 minair Compostela80.001hr 10minair Other operators, like IN, EXISTS or NOT EXISTS, > ALL, = ANY, can be used with scalar or non-scalar subqueries. Our next example uses the IN operator. Let’s suppose the person who asked about the best place for snorkeling wants to explore other destinations; in fact, they’d like to see the top three snorkeling places. The change in our subquery is clear: we only need to change “ranking_position = 1” with “ranking_position <= 3”. However, our subquery will return three records and will not be scalar anymore. We’ll use the IN operator, like so: SELECT city_destination, transportation, ticket_price, travel_time FROM one_way_ticket WHERE city_destination IN ( SELECT closest_city FROM best_10_places WHERE activity_type = 'snorkeling' AND ranking_position <= 3 ) AND city_origin = 'Paris' As in the previous example, the database first executes the subquery, which returns a list of three cities (the closest cities to the top 3 snorkeling destinations: Florianopolis, Honolulu, and Melbourne). Then the outer query is executed with these conditions: city_destination IN ('Florianopolis', 'Honolulu', 'Melbourne') The IN operator returns TRUE when the value of city_destination is one of these three cities. Thus, the main query returns the following result: City_DestinationTransportationTicket_PriceTravel_Time Florianopolisair$ 830.0011hr 30 min Honoluluair$ 1564.0015hr 20 min Melbourneair$ 2200.0018hr 50min If you want to go deeper into the nuances of SQL subqueries, check out the subqueries chapter of our interactive SQL Basics course, where you can find several examples and lots of practice exercises. Advanced Subqueries The subquery concept is easy to understand. But because of SQL’s flexibility, subqueries can be used in many different forms. Covering all the possible usages is beyond the scope of this article. What we will do instead is demonstrate some of the most important uses. How many different places can you put a subquery? Subqueries can be used in different places in a SQL query, including the WHERE, FROM, HAVING, and SELECT clauses; moreover, a subquery can also be used as part of an UPDATE, DELETE, or INSERT statement. In the next example, we will see how to use a subquery in the FROM clause. Suppose the travel agency’s owner wants to show every city along with the ticket cost and the number of “best places” near this city. To obtain the quantity of “best places” for each city, we will use a subquery (shown in blue) in the FROM clause to create a pseudo table. Then the outer query will JOIN with one_way_ticket and the pseudo table. SELECT city_destination, ticket_price, pseudo_table.quantity FROM one_way_ticket JOIN ( SELECT closest_city AS city, count(*) AS quantity FROM best_10_places GROUP BY 1 ) pseudo_table ON one_way_ticket.pseudo_table.city The result of this query is: City_DestinationTicket_PriceQuantity Florianopolis830.001 Honolulu1564.002 Melbourne2200.001 Bariloche970.002 Compostela80.001 For more on the use of subqueries in other SQL statements, read Subqueries in UPDATE and DELETE statements. This article has several examples with SQL code that’s ready to copy and paste if you want to try it out. EXISTS: A subquery-oriented operator One of the most powerful operators you can use with subqueries is the EXISTS operator. As we can see in the example below, the EXISTS operator must come before the subquery. It will return TRUE if the subquery returns at least one row – no matter what the row content is. If the subquery returns 0 rows, EXISTS will return FALSE. For the next example, let’s suppose our customer from Paris wants to travel to a place where they can do both trekking and snorkeling. The query below can be used to reply to this customer: SELECT city_destination, transportation, ticket_price, travel_time FROM one_way_ticket WHERE EXISTS ( SELECT closest_city FROM best_10_places WHERE activity_type = 'snorkeling' AND closest_city = one_way_ticket.city_destination ) AND EXISTS ( SELECT closest_city FROM best_10_places WHERE activity_type = 'trekking' AND closest_city = one_way_ticket.city_destination ) AND city_origin = 'Paris' The result shows the records related to cities with trekking and snorkeling activities: City_DestinationTransportationTicket_PriceTravel_Time Honoluluair$ 1564.0015hr 20 min One interesting point in the previous subquery is the reference to the one_way_ticket.city_destination column in the outer query. Subqueries that reference columns in the outer query are called “correlated subqueries” and have some specific behaviors. As in the previous example, correlated subqueries tend to be used with the EXISTS and NOT EXISTS subquery operators. Correlated subqueries are a powerful SQL resource. In certain scenarios, they’re the natural way to solve a problem. If you are interested in this topic, I suggest reading Correlated Subquery in SQL: A Beginner’s Guide and Learn to Write a SQL Correlated Subquery in 5 Minutes. The ALL and ANY operators This pair of operators works in conjunction with the =, <>, >, >=, < and <= operators, adding more expressivity to the language. Due the high number of possible combinations with ALL and ANY, I’ve included a table with the most common uses of these operators: ConditionReturns TRUE if ...Returns FALSE if ... Where 10 > ANY ( subquery )The subquery returns at least one value that’s greater than 10.All returned values are 10 or less. Where 10 > ALL ( subquery )The subquery returns only values greater than 10.The subquery returns at least one value of 10 or less. Where 10 = ANY (subquery)The subquery returns at least one value equal to 10.No returned values are equal to 10. Where 10 = ALL (subquery)All values returned by subquery are 10.At least one returned value is not equal to 10. Let’s apply this operator to a real-life example. Suppose we want to promote all the “world’s best places” you can visit with a ticket under $1,000. Each “best place” in the table best_10_places can have many possible tickets; we are only interested in those places where at least one ticket costs less than $1000. The query is as follows: SELECT Place_name, Activity, Ranking_position FROM best_10_places WHERE 1000 > ANY ( SELECT ticket_price FROM one_way_ticket WHERE city_destination = best_10_places.closest_city ) The results of the previous query are shown below. You can go to the best place for any activity (snorkeling, skiing, and trekking) for less than $1,000! Place_NameActivityRanking_Position Praia do Sepulturasnorkeling1 Cerro Catedralskiing1 Camino de Santiagotrekking1 Cerro Ottotrekking2 Your Next Steps with Subqueries In this article, I’ve explained subqueries and showed you several examples of how to use them. Still, this topic has plenty of variations, including the different types of subqueries and operators. To have a more complete understanding of subqueries, I suggest taking an online course like LearnSQL.com’s SQL Basics or reading the additional articles I’ve mentioned. Tags: sql learn sql subquery You may also like Learn to Write a SQL Correlated Subquery in 5 Minutes A correlated subquery is a subquery that is executed many times—once for each record (row) returned by the outer (main) query. Sounds complex? Not! Read more What Is a Nested Query in SQL? Have you ever faced a problem where it would be great to put a SELECT inside another SELECT statement? Learn how to use a nested query in SQL. Read more SQL Subqueries The article describes what a subquery is and what these useful statements look like. We will cover basic examples with the IN, EXISTS, ANY, and ALL operators, look at subqueries in FROM and WHERE clauses, and explore the difference between correlated and nested subqueries. Read more Correlated Subquery in SQL: A Beginner’s Guide Correlated subqueries are the only way to solve some SQL statements. But they can be very slow. In this post, we’ll talk about why, how, and when to use them. Read more Learn to Write a SQL Correlated Subquery in 5 Minutes A correlated subquery is a subquery that is executed many times—once for each record (row) returned by the outer (main) query. Sounds complex? Not! Read more SQL Subqueries The article describes what a subquery is and what these useful statements look like. We will cover basic examples with the IN, EXISTS, ANY, and ALL operators, look at subqueries in FROM and WHERE clauses, and explore the difference between correlated and nested subqueries. Read more Subquery vs. JOIN What are the differences between a subquery and a JOIN in SQL? How do you choose one over the other? See examples of when to use which. Read more Subquery vs. CTE: A SQL Primer SQL subqueries and CTEs seem similar, but even pros may not know all their quirks. Learn their similarities, differences, and best use cases. Read more Beginner’s Guide to the SQL Subquery Did you know that you can use a SQL query inside another SQL query? Find out the ABCs of subqueries and improve your SQL skills! Read more What Is a Nested Query in SQL? Have you ever faced a problem where it would be great to put a SELECT inside another SELECT statement? Learn how to use a nested query in SQL. Read more Converting Subqueries to Joins No more badly performed queries! Find out how you can convert SQL subqueries to joins and improve your query efficiency. Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.