inner join VS. subquery - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

inner join VS. subquery

Author

Topic

Juls
Yak Posting Veteran

51 Posts

Posted - 2003-09-24 : 14:59:03

Hi,
I need some advice:

What is more efficient time and memory and processing wise:

Build a query using inner join like
select c.*
from customers c join orders o on c.id = o.custid
where o.date >'7/7/03'

or using a subquery like
select *
from customers c
where c.id in
(select o.custid
from orders o
where o.date >'7/7/03')

This is just a simple example. I am more curious about the effect on large complex queries.

Thanks so much.

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-24 : 15:04:57

Did you do a show plan?

Also the join will give you access to columns in the select that the sub select will not.

Also (again) a lot has to do with what indexes you have and what they contain...

And did you forget EXISTS?

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-24 : 15:17:01

[code]
USE Northwind
GO

SELECT c.*
FROM customers c join orders o on c.CustomerID = o.CustomerID
WHERE o.OrderDate >'1/1/97'

SELECT c.*
FROM customers c
WHERE c.CustomerID IN (SELECT o.CustomerID
FROM orders o
WHERE o.OrderDate >'1/1/97')

SELECT c.*
FROM customers c
WHERE EXISTS (SELECT 1
FROM orders o
WHERE o.OrderDate >'1/1/97'
AND o.CustomerID = c.CustomerID)
[/code]

quote:

SET STATISTICS PROFILE ON SQL:StmtCompleted 0 0 0 0
SELECT c.* FROM customers c join orders o on c.CustomerID = o.CustomerID WHERE o.OrderDate >'1/1/97' SQL:StmtCompleted 235 0 136 0
SELECT c.* FROM customers c WHERE c.CustomerID IN (SELECT o.CustomerID FROM orders o WHERE o.OrderDate >'1/1/97') SQL:StmtCompleted 31 0 232 0
SELECT c.* FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.OrderDate >'1/1/97' AND o.CustomerID = c.CustomerID) SQL:StmtCompleted 31 0 232 0
SET STATISTICS PROFILE OFF SQL:StmtCompleted 0 0 0 0

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-24 : 17:20:10

note that the first one -- customers joined to orders -- may return duplicates of each customer if there are more than 1 order, so it is really not the same query.

- Jeff

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2003-09-25 : 01:31:48

test the two solutions to find out which is better.

Turn on the Execution Plan from the Query menu in QA as well as setting STATISTICS IO ON and STATISTICS TIME ON.

look at the statistics and determine which is better. Make sure you index the keys you are joining on to make sure that your queries run optimally.

My gut instinct says that the join is better, but you never know unless you test.

-ec

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2003-09-25 : 01:44:15

quote:
Originally posted by jsmith8858

note that the first one -- customers joined to orders -- may return duplicates of each customer if there are more than 1 order, so it is really not the same query.

- Jeff

Good point

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources