Difference between Sub Query and Join in SQL Server

Last Reply 2 months ago By dharmendr

Posted 2 months ago

Below mentioned are the queries for same result set but different sql methods are used, please tell me which is the most efficient way of acheiving the task and how they are different from one another?

 Queries are: 

SELECT Name FROM Customers WHERE EXISTS 
(SELECT Item FROM Orders 
WHERE Customers.ID = Orders.ID AND Price < 50)

SELECT Name FROM Customers 
INNER JOIN Orders ON Customers.ID = Orders.ID 
WHERE Orders.Price < 50

SELECT Name FROM Customers WHERE Customers.ID IN
(SELECT Orders.ID FROM Orders 
WHERE Customers.ID = Orders.ID AND Price < 50)

SELECT Name FROM Customers WHERE Customers.ID ALL = 
(SELECT Orders.ID FROM Orders WHERE Customers.ID = Orders.ID AND Price < 50)

 

Posted 2 months ago

Hi EmadKhan,

The query you are using is Sub-Query and Join.

According to MSDN, in sql server in most cases there is usually no performance difference between queries that uses sub-queries and equivalent queries using joins. 

But in some cases where existence must be checked, a join produces better performance. Otherwise the nested query must be processed for each result of the outer query. In such cases, a join approach would produce better results.

To Check efficient way use Execution Plan to see how your database executes the query on your data.

In general joins work faster than sub-queries but in reality it all depends on the execution plan that is generated by SQL Server.

For more details and performance refer below links.

http://csharp-video-tutorials.blogspot.com/2013/01/what-to-choose-for-performance.html

https://www.c-sharpcorner.com/article/comparison-of-subquery-and-joins/