How To Convert SQL Subquery To Join?

By /
How To Convert Subquery To Join

The subqueries in SQL are the queries that are inside another query. This query can be set inside any clause like WHERE, HAVING, FROM, etc. These subqueries are likewise known as nested queries. The major aim of subqueries is to retrieve data from the table. It is also used as a condition to fetch the specific data from the table.

The Join clause is used to combine the data elements from different tables. There are four fundamental types of Joins known in SQL. The Inner Join, Full Join, Left Join, and Right Join. You can discover more about the Join clause here. Now, this article concentrates on how to substitute the subqueries with Joins in SQL. But first, let’s understand why we need this replacement.

Why We Need to Replace Subqueries With Joins?

The Join clause is used to combine the data from two or more tables. It allows to access data from the table. On the other side, we use Subqueries for filtering purposes. Subqueries will also permit to access the data from the table. Joins are easy to use because they don’t need any extra filtering to retrieve the data from the table. Hence, the Joins are more convenient to operate over subqueries. When we deal with large datasets with extensive numbers of data points then, the Joins are more efficient to operate compared to the Subqueries.

Advantages of Using Joins Over Subqueries

  • We can easily replace the Subquery with Join.
  • The Join works faster and shows the best results for large databases.
  • There are no complications of the filtrations like subqueries.
  • The Join is simple compared to the subquery.
  • The subquery includes multiple queries and more complex code.
  • The Joins require less calculations as compared to subqueries. The logic is simple and easy for JOIN.

Also Read: Join Vs Subqueries in MySQL to understand the differences, limitations and advantages of both.

Methods To Replace Subqueries With Joins

The different types of Joins can substitute the subqueries and accomplish their task in SQL. Below are some examples where we have used Joins like Left Join, Right Join, Inner Join, and Full Join. Let’s try to execute each example and understand the approach.

1. Replace Subquery With LEFT Join

We have used two different tables (Customerstable and Ordertable). These tables contain some information and data points. In example 1, we have used LEFT JOIN in place of Subqueries. The Left Join is used to access all the elements from the left table and match elements from the right table (Here, it is Customerstable). First, we will implement the code using subquery and then with the LEFT JOIN.

SELECT 
    O.OrderID,
    O.orderDate,
    (SELECT Name FROM Customerstable C WHERE C.ID = O.CustomerID) AS Name
FROM Ordertable O;Code language: SQL (Structured Query Language) (sql)
Subquery Code For Example 1

Now, let’s try to execute this with LEFT JOIN.

SELECT 
    O.OrderID,
    O.Orderdate,
    C.Name
FROM Ordertable O
LEFT JOIN Customerstable C ON O.CustomerID = C.ID;Code language: SQL (Structured Query Language) (sql)
LEFT JOIN Example

You can see the results are the same for both the codes. Let’s try this for other joins.

2. Replace Subquery With Right Join

Here, we used RIGHT JOIN instead of subquery to execute an example. Right Join will help you to fetch the data points/ elements from the right table and match elements from the left table (Here, it is ordertable).

SELECT 
    C.ID,
    C.Name,
    (SELECT COUNT(*) FROM Ordertable O WHERE O.CustomerID = C.ID) AS OrderCount
FROM Customerstable C;Code language: SQL (Structured Query Language) (sql)

Let’s see the result of this code.

Subquery Code For Example 2

Now, the subquery will be replaced by Right Join.

SELECT 
    C.ID,
    C.Name,
    COUNT(O.OrderID) AS OrderCount
FROM Customerstable C
RIGHT JOIN Ordertable O ON C.ID = O.CustomerID
GROUP BY C.ID, C.Name;Code language: SQL (Structured Query Language) (sql)
Right Join Example

The results are the same.

3. Replace Subquery With FULL Join

Example 3 will execute the FULL JOIN in place of the subquery. The Full Join will help to join all the rows from all the tables. The limit for subquery is set to 1 so that it will fetch only one orderdate. Similarly, we will execute the Full Join. Let’s see the query.

SELECT 
    C.Name,
    (SELECT OrderDate FROM Ordertable O WHERE O.CustomerID = C.ID LIMIT 1) AS OrderDate
FROM Customerstable C;Code language: SQL (Structured Query Language) (sql)

This code will return the result of every customer name with the order date.

Subquery Code For Example 3
SELECT 
    C.Name,
    O.OrderDate
FROM Customerstable C
FULL JOIN Ordertable O ON C.ID = O.CustomerID;Code language: SQL (Structured Query Language) (sql)
FULL Join Example

The results are the same for both the codes.

4. Replace Subquery With Inner Join

In example 4, we executed the Inner Join in place of the subquery. The Inner Join will help to fetch the match elements from all the rows of the table. Here, the limit is set to 1 for subquery. Let’s see both the query and their results.

SELECT 
    C.Name,
    (SELECT OrderDate FROM Ordertable O WHERE O.CustomerID = C.ID LIMIT 1) AS OrderDate
FROM Customerstable C;Code language: SQL (Structured Query Language) (sql)
Subquery Code For Example 4

Let’s see the result of the Inner Join query.

SELECT 
    C.Name,
    O.OrderDate
FROM Customerstable C
INNER JOIN Ordertable O ON C.ID = O.CustomerID;Code language: SQL (Structured Query Language) (sql)

Let’s check the result.

Inner Join Example

These results are the same.

Summary

This article focuses on the concept of replacing the subquery with the JOIN. We have covered multiple examples with all basic JOINS. The examples will give a brief idea about how the results are the same for the subquery query and Join query. The advantages of it are also explained in this article. Hope you will enjoy this writing.

Reference

https://stackoverflow.com/questions/600943/how-to-convert-a-sql-subquery-to-a-join