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 Subquery, Correlated Subquery and Join Operation
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.
Contact Us