Choosing Joins Over Subqueries

Hello Readers !!!

Colleagues @ my office always ask me - "When you can fetch the data from the database table using subqueries, then what is the need of joins".

And I am feed up of answering them again and again. And that has inspired me to write this post.

The data what you fetch is the same, irrespective of the methodology used. But the difference occurs in the time used to fetch the records.

In order to explain this properly, I have a simple example.

Example:

- Imagine you have a Employee table, with employee id as its primary key. And you also have Departments table with department id as its primary key respectively.

- Use the following example to see the difference by yourself:

BEGIN


DECLARE @START_TIME_1 DATETIME,@END_TIME_1 DATETIME,@START_TIME_2 DATETIME,
@END_TIME_2 DATETIME,@TOTAL_TIME_1 DATETIME,@TOTAL_TIME_2 DATETIME;


SELECT EMP.EMPID , EMP.EMPNAME, DEPT.DEPARTMENT_NAME
FROM EMPDETAILS EMP, DEPARTMENT DEPT
WHERE EXISTS
(
   SELECT DEPTID FROM DEPARTMENT DEPT
   WHERE EMP.DEPTID = EMP.DEPTID
);

SET @END_TIME_1 = GETDATE();
SET @TOTAL_TIME_1 = DATEDIFF(millisecond,@START_TIME_1,@END_TIME_1); 

SET @START_TIME_2 = getdate();

SELECT EMP.EMPID, EMP.EMPNAME, DEPT.DEPARTMENT_NAME
FROM EMPDETAILS EMP
INNER JOIN DEPARTMENT DEPT
ON EMP.DEPT_ID = DEPT.DEPTID;
SET @END_TIME_2 = getdate();
SET @TOTAL_TIME_2 = DATEDIFF(millisecond,@START_TIME_2,@END_TIME_2);


SELECT @START_TIME_1 AS [FIRST_QUERY_START];
SELECT @END_TIME_1 AS [FIRST_QUERY_END];

SELECT @START_TIME_2 AS [SECOND_QUERY_START];
SELECT @END_TIME_2 AS [SECOND_QUERY_END];

SELECT @TOTAL_TIME_1 AS [FIRST_QUERY_TOTAL];
SELECT @TOTAL_TIME_2 AS [SECOND_QUERY_TOTAL];


END

Just see the difference in milliseconds, or even seconds in FIRST_QUERY_TOTAL and SECOND_QUERY_TOTAL columns.

Following are the proposed outputs.
1. Both the columns will display same values, if the amount of data (in the above tables described) is less.
2. But as the number of records in the tables increases, joins become more efficient than subqueries.

- So when the data to be fetched is to be fetched from a table with huge records, then prefer joins over sub-queries.


If you like this post, then please pray for the victims of 26/11 attack @ Mumbai.


Thanking You,
Akshay Sahu
(MCTS SQL Server 2005, OCJP [Java SE 6])SET @START_TIME_1 = GETDATE();