Open In App

Difference between Nested Subquery, Correlated Subquery and Join Operation

Improve
Improve
Improve
Like Article
Like
Save Article
Save
Share
Report issue
Report

Joins are used to combine two or more different tables based on a common field between them with the help of this we can easily retrieve the data from multiple tables. So, In this article, we are going to discuss the JOIN Operation, and Subquery in detail. Let’s start with the JOIN operation.

JOIN Operation

A join operation is a binary operation used to combine data or rows from two or more tables based on a common field between them. INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN are different types of Joins.

Example:

Orders (OrderID , OrderDate);
Customers (CustomerID, CustomerName, ContactName, Country);

Find details of customers who have ordered.

Query

SELECT * from Customers JOIN Orders 
ON Orders.OrderID=Customers.CustomerID;

Output

output

output

Subquery

CREATE TABLE Customers(
CustomerID INT PRIMARY KEY NOT NULL,
CustomerName VARCHAR(50),
ContactName VARCHAR(50),
Country VARCHAR(50);

CREATE TABLE Orders(
OrderID INT ,
OrderDate DATE);

INSERT INTO Orders(OrderID,OrderDate)
VALUES(1,'11/12/2020'),
(2,'14/12/2014'),
(3,'17/1/2019'),
(4,'18/2/2020');

INSERT INTO Customers(CustomerID,CustomerName, ContactName,Country)
VALUES (1,'John','David','USA'),
(2,'Leo','David','UK'),
(3,'David','John','USA'),
(4,'Betty','Leo','UK'),
(5,'Peter','Peter','UAE');

Customers table:

Customer Table

Customer Table

Orders table:

Order Table

Order Table

Nested Subquery

In Nested Query, the Inner query runs first, and only once. Outer query is executed with the result from Inner query. Hence, the Inner query is used in execution of the Outer query.

Example:

Orders (OrderID, CustomerID, OrderDate);
Customers (CustomerID, CustomerName, ContactName, Country);

Find details of customers who have ordered.

Query

SELECT * FROM Customers WHERE 
CustomerID IN (SELECT CustomerID FROM Orders);

Output

output

output

Correlated Subquery

In Correlated Query,  Outer query executes first and for every Outer query row Inner query is executed. Hence, the Inner query uses values from the Outer query.

Example:

Orders (OrderID , OrderDate);
Customers (CustomerID, CustomerName, ContactName, Country);

Find details of customers who have ordered.

Query

SELECT * FROM Customers where 
EXISTS (SELECT CustomerID FROM Orders
WHERE Orders.OrderID=Customers.CustomerID);

Output

output

output

Application of Join Operation and Subquery

To understand the difference between Nested Subquery, Correlated Subquery, and Join Operation firstly we have to understand where we use subqueries and where to use joins.

  • When we want to get data from multiple tables we use join operation. Example: Let’s consider two relations as:
    Employee (eId, eName, eSalary, dId);
    Department (dId, dName, dLocation);

    Now, we have to find employee names and Department name working at London Location. Here, we have to display eName from employee table and dName from Department table. Hence we have to use Join Operation.

    SELECT e.eName, d.dName from Employee e, 
    Department d
    where e.dId=d.dId and d.dLocation="London";
  • When we want to get data from one table and the condition is based on another table we can either use Join or Subquery. Now, we have to find employee names working at London Location. Here, we have to display only eName from the employee table hence we can use either Join Operation or Subquery Using Join Operation:
    SELECT e.eName from Employee e, Department d 
    where e.dId=d.dId and d.dLocation="London";

    Using Subquery:

    SELECT eName from Employee 
    where dId=(SELECT dId from Department where dLocation="London");

    After understanding the basic difference between Join and Subqueries, Now we will understand the difference between Nested Subquery, Correlated Subquery, and Join Operation.

Difference between Nested Query, Correlated Query, and Join Operation

Parameters Nested Query Correlated Query Join Operation
Definition In Nested query, a query is written inside another query and the result of the inner query is used in the execution of the outer query.  In Correlated query, a query is nested inside another query and an inner query uses values from the outer query.                     The join operation is used to combine data or rows from two or more tables based on a common field between them. INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN are different types of Joins.
Approach Bottom-up approach i.e. Inner query runs first, and only once. The outer query is executed with result from the Inner query. Top to Down Approach i.e. Outer query executes first and for every Outer query row Inner query is executed. It is basically cross product satisfying a condition.
Dependency Inner query execution is not dependent on Outer query. Inner query is dependent on the Outer query. There is no Inner Query or Outer Query. Hence, no dependency is there.
Performance  Performs better than Correlated Query but is slower than Join Operation. Performs slower than both Nested Query and Join operations as for every outer query inner query is executed. By using joins we maximize the calculation burden on the database but joins are better optimized by the server so the retrieval time of the query using joins will almost always be faster than that of a subquery.

Conclusion

In this article, we have basically mentioned the JOINS, nested query, and co-related query, and the differences between them. Both the topics, whether the JOINS or the Subquery, play an important role in the Structured query language. A nested query is used to retrieve data based on the result of an inner query and a correlated query is a type of nested query where the inner query references a column from the outer query and a join operation is used to merges the two different tables based on a common field between them. We discussed the JOINS and subquery with suitable examples.



Last Updated : 28 Aug, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads