CODEWITHSUNDEEP
sql-servert-sql
Jakouza
Jakouza

Reputation: 29

Difference between Join and Subquery in SQL Server 2017

This is my first query:

select 
    r.[ProductID], [Name],
    [Color], [UnitPrice] 
from 
    [Production].[Product] r  
inner join 
    [Sales].[SalesOrderDetail] d on d.productid = r.productid

This is my second query:

select [ProductID], [Name], [color] 
from [Production].[Product] o 
where [ProductID] in (select [UnitPrice] 
                      from [Sales].[SalesOrderDetail] s
                      where s.ProductID = o.ProductID)

The first query returns correctly what I want to achieve, but the second query returns null. I want to use the second query to have the same result. Is that possible?

Upvotes: 0

Views: 40

Answers (1)

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You can use exists :

select p.*
from product p
where exists (select 1 from SalesOrderDetail sd where sd.productid = p.product);

Your second query is not same as first query. It is comparing ProductID against UnitPrice which is invalid. Should produce no result.

For original version of query you can just change the sub query unit price with Productid :

where [ProductID] in (select sd.productid from SalesOrderDetail sd)

However, if you want unitprice (which is available in SalesOrderDetail) then this subquery will not help you. You need JOIN instead.

Upvotes: 1

Related Questions