When one query is written within another query It is called?
SQL subqueries are basic tools if you want to communicate effectively with relational databases. In this article, I provide five subquery examples demonstrating how to use scalar, multirow, and correlated subqueries in the WHERE, FROM/JOIN, and SELECT clauses. Show
A subquery, or nested query, is a query placed within another SQL query. When requesting information from a database, you may find it necessary to include a subquery into the There are several types of SQL subqueries:
You can read more about the different types of SQL subqueries elsewhere; here, I want to focus on examples. As we all know, it’s always easier to grasp new concepts with real-world use cases. So let’s get started. Let’s say we run an art gallery. We have a database with four tables:
Now let’s explore this data using SQL queries with different types of subqueries. Example 1 - Scalar SubqueryWe’ll start with a simple example: We want to list paintings that are priced higher than the average. Basically, we want to get painting names along with the listed prices, but only for the ones that cost more than average. That means that we first need to find this average price; here’s where the scalar subquery comes into play: SELECT name, listed_price FROM paintings WHERE listed_price > ( SELECT AVG(listed_price) FROM paintings ); Our
subquery is in the
If this seems a bit complicated, you may want to check out our interactive SQL Basics course and brush up on your essential SQL skills. Examples 2 – Multirow SubqueryNow let’s look into subqueries that return one column with multiple rows. These subqueries are often included in the Suppose we want to list all collectors
who purchased paintings from our gallery. We can get the necessary output using a multirow subquery. Specifically, we can use an inner query to list all collectors’ IDs present in the SELECT first_name, last_name FROM collectors WHERE id IN ( SELECT collector_id FROM sales ); And here’s the output:
Interestingly, we could get the same result without a subquery by using an Here’s the query: SELECT DISTINCT collectors.first_name, collectors.last_name FROM collectors JOIN sales ON collectors.id = sales.collector_id; You can read more about choosing subquery vs. JOIN elsewhere in our blog. Example 3 – Multirow Subquery with Multiple ColumnsWhen a subquery returns a table with multiple rows and multiple columns, that subquery is usually found in the Let’s say that we want to see the total amount of sales for each artist who has sold at least one painting in our gallery. We may start with a subquery that draws on the SELECT artists.first_name, artists.last_name, artist_sales.sales FROM artists JOIN ( SELECT artist_id, SUM(sales_price) AS sales FROM sales GROUP BY artist_id ) AS artist_sales ON artists.id = artist_sales.artist_id; We assign a
meaningful alias to the output of our subquery ( Here’s the result of the query:
So, within one short SQL query, we were able to calculate the total sales for each artist based on the raw data from one table ( Subqueries can be quite powerful when we need to combine information from multiple tables. Let’s see what else we can do with subqueries. Example 4 – Correlated SubqueryThe following example will demonstrate how subqueries:
For each collector, we want to calculate the number of paintings purchased through our gallery. To answer this question, we can use a subquery that counts the number of paintings purchased by each collector. Here’s the entire query: SELECT first_name, last_name, ( SELECT count(*) AS paintings FROM sales WHERE collectors.id = sales.collector_id ) FROM collectors; Notice how the inner query in this example actually
runs for each row of the
Here’s the output:
As you see, the output of the subquery (i.e. the number of paintings) is different for each record and depends on the output of the outer query (i.e. the corresponding collector). Thus, we are dealing with a correlated subquery here. Check out this guide if you want to learn how to write correlated subqueries in SQL. For now, let’s have one more correlated subquery example. Example 5 – Correlated SubqueryThis time, we want to show the first names and the last names of the artists who had zero sales with our gallery. Let’s try to accomplish this task using a correlated subquery in the SELECT first_name, last_name FROM artists WHERE NOT EXISTS ( SELECT * FROM sales WHERE sales.artist_id = artists.id ); Here is what's going on in this query:
In our example, we have only one artist without any sales yet. Hopefully, he’ll land one soon. It’s Time to Practice SQL Subqueries!In this article, I’ve covered several SQL subquery examples to give you a general understanding of how subqueries can be leveraged in SQL. However, often common table expressions (CTEs) can do better than subqueries. If you want to practice SQL subqueries and other foundational SQL topics, try our SQL Basics interactive course. It includes 129 coding challenges on querying multiple tables, aggregating and grouping data, joining tables, writing subqueries, and much more. Want to become a SQL master? Check out our SQL From A to Z learning track. It goes beyond the basics and includes 7 interactive courses covering standard SQL functions, SQL data manipulation language (DML), basic SQL reports, window functions, common table expressions (CTEs), and GROUP BY extensions. Thanks for reading, and happy learning! When you place one query inside another the inner query is called?A subquery is a complete query nested in the SELECT,FROM, HAVING, or WHERE clause of another query.The subquery must be enclosed in parentheses and have a SELECT and a FROM clause, at a minimum.
When one query is written with another it is known as?A nested query is a query that has another query embedded within it. The embedded query is called a subquery. A subquery typically appears within the WHERE clause of a query. It can sometimes appear in the FROM clause or HAVING clause.
What is query and nested query?Using Nested Queries with SELECT. In SQL, a query is an operation that retrieves data from a table in a database and always includes a SELECT statement. A nested query is a complete query embedded within another operation.
What is a nested query?A nested query consists of two or more ordinary queries nested in such a way that the results of each inner query ( subselect ) are used in the comparison test for the selection clause of the next outer query (or another command statement).
|