Difference between Sub Query and Join in SQL Server

EmadKhan

SOLVED CLOSED ACTIVE ANSWERED
User: EmadKhan
Posted: on Jul 29, 2019 12:43 AM
Forum: SQL Server
Replies: 1
Views: 2542

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)

dharmend...

0 1

Reply
User: dharmendr
Replied: on Jul 29, 2019 01:09 AM
Report

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/

Difference between Sub Query and Join in SQL Server

Company

Explore

Follow us

Contact Us