Introduction to SQL Subqueries
If you’re familiar with the basics of SQL databases, you’ll know that using SQL queries is a critical part of working with them. SQL queries let you ask the database for specific data and get the results you need.
But have you ever wondered if there’s a way to perform queries within queries? That’s where SQL subqueries come in.
In this article, we’ll explore the purpose and types of SQL subqueries, with a closer look at scalar subqueries. We’ll then proceed to use a scalar subquery in an example scenario to find paintings that are above average price.
So, let’s get started. What are SQL Subqueries?
SQL subqueries, or nested queries, are queries that exist within other queries. They’re used to create more complex queries that allow you to access data that wouldn’t be accessible through a single query.
The results of subqueries can be used in expressions or passed on to other queries, making them a powerful tool in any database developer’s arsenal.
Types of SQL Subqueries
There are three types of SQL subqueries, namely scalar, multirow, and correlated subqueries.
Scalar Subqueries
A scalar subquery returns a single value and is usually used in expressions. They’re often used to retrieve a single value needed in other parts of a query.
For example, you can use a scalar subquery to find the average price of paintings in the database. This value can then be used in your main query as an expression to compare prices against.
Multirow Subqueries
A multirow subquery returns multiple rows of values that can be used in expressions or passed onto the next query. They’re used when you need to select multiple rows of data that aren’t available in the main query.
For example, you can use a multirow subquery to find all the genres assigned to each painter in your database.
Correlated Subqueries
A correlated subquery is a type of nested query that relies on data from the main query to execute. For instance, to find all paintings whose price is higher than the average price, you can use a correlated subquery that references the current row values.
Correlated subqueries are useful when the query requires data from both the subquery and the main query, such as when working with a one-to-many relationship between tables.
Example 1: Scalar Subquery
In this example, we’ll use a scalar subquery to find all paintings whose listed price is higher than the average price of paintings in the database.
SELECT *
FROM paintings
WHERE listed_price > (
SELECT AVG(listed_price)
FROM paintings
);This query will find all paintings whose listed price is higher than the average price of paintings in the database. The scalar subquery inside the parentheses returns the average listed price, which can be used as a constant to compare against each listed price in the main query.
The WHERE clause filters out results that don’t meet the comparison criteria.
Conclusion
SQL subqueries are a powerful and versatile tool that can help you write more complex and efficient queries. Use this guide to understand the different types of SQL subqueries, with a focus on scalar subqueries and their use in expressions.
By using subqueries wisely, you can access data that may not be available otherwise and get better insights into your data.
Example 2: Multirow Subquery
Now that we’ve explored scalar subqueries, let’s dive into multirow subqueries.
Multirow subqueries return multiple rows of values, making them extremely useful when you want to retrieve a list of results that don’t exist in the main query. In this example, we’ll use a multirow subquery to find all collectors who have purchased paintings from our database.
First, we need to understand the structure of our paintings and collectors tables. The paintings table contains information about the paintings, while the collectors table contains information about the collectors who’ve purchased them.
The two tables are connected by a foreign key in the collectors table that references the painting ID in the paintings table. Here’s how we’ll use a multirow subquery to find all collectors who have purchased paintings:
SELECT *
FROM collectors
WHERE collector_id IN (
SELECT collector_id
FROM purchases
WHERE painting_id IN (
SELECT painting_id
FROM paintings
)
);In this query, the multirow subquery returns a list of collector IDs who’ve purchased paintings from a list of painting IDs retrieved from the paintings table. The main query then filters out the collectors who don’t appear in the list using the WHERE clause and the IN operator.
While the above query works, we can achieve the same result more efficiently by using INNER JOIN.
SELECT collectors.*
FROM collectors
INNER JOIN purchases ON collectors.collector_id = purchases.collector_id
INNER JOIN paintings ON purchases.painting_id = paintings.painting_id;By using INNER JOIN, we don’t need to use a subquery to retrieve the painting IDs from the paintings table. Instead, we can directly join the collectors and purchases table using the collector_id foreign key and then join the paintings table using the painting_id foreign key in the purchases table.
This query returns an identical result to the previous query, but it’s more efficient.
Example 3: Multirow Subquery with Multiple Columns
In this example, we’ll look at how we can use a multirow subquery in the FROM/JOIN clause to calculate total sales for each artist in our paintings database.
We’ll create a summary table that lists the artist ID, artist name, and total sales for each artist. To do this, we need to join the paintings, artists, and purchases tables using the painting ID and artist ID foreign keys.
SELECT artists.artist_id, artists.artist_name, summary.total_sales
FROM artists
JOIN (
SELECT paintings.artist_id, SUM(purchases.price) as total_sales
FROM paintings
JOIN purchases ON purchases.painting_id = paintings.painting_id
GROUP BY paintings.artist_id
) as summary ON summary.artist_id = artists.artist_id;In this query, we use a multirow subquery to join the paintings and purchases tables and calculate the total sales for each artist using the SUM function. We group the subquery results by artist ID and rename the SUM function output to total_sales.
We then join the summary subquery with the artists table using the artist ID foreign key to retrieve the artist name and final total_sales columns. Note that we use the alias “summary” to refer to the subquery output in the main query, as the multirow subquery returns multiple columns.
Conclusion
Multirow subqueries can be incredibly useful for retrieving lists of results that don’t exist in the main query. Whether you’re looking for collectors who’ve purchased paintings or calculating total sales for each artist, multirow subqueries can provide you with the data you need.
By using subqueries and various SQL techniques like INNER JOIN, you can optimize your queries and get the most out of your database.
Example 4: Correlated Subquery
Correlated subqueries are used when we need to reference a value in the outer query from the inner subquery.
In this example, we’ll use a correlated subquery in the SELECT clause to calculate the number of paintings purchased by each collector in our database. To begin, we’ll join the collectors and purchases tables using the collector ID foreign key.
SELECT collectors.collector_name,
(SELECT COUNT(*)
FROM purchases
WHERE purchases.collector_id = collectors.collector_id) AS quantity_purchased
FROM collectors;In this query, the inner subquery runs once for each row in the result set of the outer query. The subquery counts the number of purchases by referencing the collector ID from the outer query.
Finally, we select the collector name from collectors and the subquery result, which is the count of the purchases made by the collector.
Example 5: Correlated Subquery
In this next example, we’ll explore how to use a correlated subquery in the WHERE clause to find artists with zero sales.
To do this, we’ll join the paintings and artists tables using the artist ID foreign key and the purchases table using the painting ID foreign key. We’ll then use a correlated subquery in the WHERE clause to filter out the artists with no sales.
SELECT artists.artist_name
FROM artists
LEFT JOIN paintings ON paintings.artist_id = artists.artist_id
LEFT JOIN purchases ON purchases.painting_id = paintings.painting_id
WHERE purchases.collector_id IS NULL;In this query, we use a LEFT JOIN to join the artists, paintings, and purchases tables. The LEFT JOIN ensures that we retain all the rows from the artists table even if there are no matching rows in the paintings or purchases table.
This provides us with a complete list of all the artists in the database. We then use a correlated subquery in the WHERE clause to filter out any artists with sales.
The subquery checks if the collector ID column in the purchases table is null, which indicates that the artist has made no sales. By using a correlated subquery in this way, we can filter out the artists with zero sales from our result set.
Conclusion
Correlated subqueries can be a powerful tool when we need to reference values from the outer query in the inner subquery. By using correlated subqueries in different parts of our SQL queries, we can build more complex and sophisticated queries that can return insightful results.
These subqueries, when used correctly, can take our data analysis to the next level.
Conclusion and Learning Resources
Subqueries are an incredibly useful tool for pulling specific data from a database. They allow developers to write more complex queries and retrieve data that wouldn’t be possible otherwise.
However, there are some limitations to using subqueries. For example, subqueries often slow down query execution time, especially when working with large databases.
Developers should be aware of these limitations and use them judiciously. Another technique for working with SQL databases is common table expressions (CTEs).
CTEs are similar to subqueries, but they allow you to create temporary named result sets that you can reuse throughout a query. CTEs can improve query performance by reducing code duplication and making queries more readable.
To master the basics of SQL and subqueries, there are many excellent online courses available. SQL Basics and SQL From A to Z are two popular courses for beginners that cover the basics of SQL and subqueries.
These courses can help you develop the skills needed to work with SQL databases and master the use of subqueries.
In conclusion, subqueries are a powerful tool for working with SQL databases.
They can help you retrieve specific data while creating more complex queries. Subqueries, while with limitations, are used judiciously to optimize query performance.
Common table expressions can help write queries that are more efficient and easier to read.
Learning SQL with online courses can aid you in developing your skills to work with SQL databases and subqueries effectively.
In summary, subqueries are a critical tool in working with SQL databases. Subqueries allow developers to retrieve specific data and create more complex queries.
There are three types of subqueries- scalar, multirow, and correlated. Scalar subqueries return a single value required in other parts of a query.
Multirow subqueries are used when you need to select multiple rows of data that aren’t available in the main query. Correlated subqueries, on the other hand, are used when you reference the outer query’s values from the inner subquery.
SQL databases have limitations, and subqueries should be used judiciously. Also, developers can use common table expressions to improve query performance.
To learn the basics of SQL and subqueries, online courses like SQL Basics and SQL From A to Z offer excellent resources.
Ultimately, subqueries are critical tools for developers to work with databases effectively and retrieve specific data through complex queries.