When it comes to working with relational databases, you need to master how to manipulate and combine the queries to perform the tasks that you wish. That is why each relational database engine implements its flavor of the SQL language by attempting to provide exceptional features, efficiency, and ease of use for its users.
One of the most powerful features of SQL is subqueries. Subqueries are a set of nested queries within a larger and more complex query. Subqueries allow you to retrieve the data or perform more complex operations as a single entity.
We can use the subqueries to perform data filtering, sorting, aggregation on multiple tables, and more.
However, another feature that is hidden under SQL subqueries is known as subquery joins. These are similar to subqueries; instead, they are joins that allow you to use the subqueries within an outer query to join the tables together.
If your head is spinning, don’t worry because subquery joins can be challenging to master, especially in the beginning. However, this tutorial attempts to break down the subquery joins in elementary steps without leaving too much to chance.
Keep in mind that we assume that you are not new to SQL, SQL Joins, SQL Subqueries, or such. However, if you are, check our tutorials on the topics to discover more.
Master the SQL Outer Joins
If you want to understand how to work with subquery joins, learning how to work with the outer joins is a key factor.
If you are unfamiliar, an SQL outer join lets you fetch all the rows from one table and the matching rows from the second table. It’s a little more complex than that, including the left outer join, right outer join, full outer join, etc.
In an SQL left outer join, the query returns all the rows from the left table and the matching rows from the right table. The query includes the NULL values in the resulting columns if there are no matching rows in the right table.
In the case of a right outer join, the query returns all the rows from the right table but only the matching rows from the left table. Similarly, the query includes the NULL values if there are no matching rows from the left table.
Finally, we have the full outer join. This join returns all the rows from the right and left tables and the NULL values for any non-matching records.
SQL Subquery Joins
Now that we understand about the SQL subqueries, let’s talk about subquery joins. Subquery joins allow us to use the subqueries within an outer query to join the tables.
If you are asking, is that it? Yes, that is all that subquery joins do.
To better demonstrate this, take the following syntax example that is demonstrated in the following:
FROM table1
LEFT OUTER JOIN (
SELECT column1, column2
FROM table2
) AS subquery
ON table1.column3 = subquery.column1;
In the previous syntax, we select all the columns from table one using a left outer join to join it with a subquery. The role of the subquery is to fetch the defined columns from Table 2. We then join it with Table 1 on the condition of column 2 from Table 1 and column 1 from the subquery.
Practical Example:
Theoretically, it seems less intuitive but let us take a real-world scenario by taking the Sakila database.
Suppose we want to retrieve the list of all the films in the database and their respective languages. The languages of the films are stored in the language table, and the film names are stored in the film table.
However, the film table has a foreign key called the “language_id” column from the language table. Therefore, we can use a subquery join with a left outer join to join the two tables together as demonstrated in the following query:
FROM film f
LEFT OUTER JOIN (
SELECT language_id, name
FROM language
) AS l
ON f.language_id = l.language_id;
In the previous example query, we select the title column from the film table and the name column from the language table.
We then use a subquery to select the language_id and the name column from the languages table. The next step is to join it with the film table on the condition that the language_id from the film table equals the language_id from the language table.
To ensure that all the films are included in the result, we need to use the left outer join which consists of all the results from the left table which, in this case, is the film table.
An example output is as follows:
We can also do the same with a right outer join. The syntax is as follows:
FROM table1
RIGHT OUTER JOIN (
SELECT column1, column2
FROM table2
) AS subquery
ON table1.column3 = subquery.column1;
This behaves similarly but includes all the records in the right table even if there are no matching records.
Useful Things to Know
It is good to understand that although the subquery joins are incredibly useful and can optimize your workflow, use them with caution.
For example, avoid using the subquery joins with a large data set. This is because they can return the extensive records that can impact the database performance.
Consider using the query analyzing tools before executing any subquery joins.
Conclusion
This tutorial explored the fundamentals of working with subqueries and subquery joins in SQL. At the end of this tutorial, you now understand how to work with subquery joins, why you may need to use them, and a practical example of how they can help you in your workflow.
