Should I use the inner join? - 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

SQL Server Development (2000)

Should I use the inner join?

Author

Topic

de9625
Starting Member

17 Posts

Posted - 2004-02-19 : 08:05:30

I have two statements that will give me the same resultset. When should I use they first or the second statement? Does this depend on the size of the two tables?

Anyone who wants to explain the difference?

SELECT I.ITEM_NO, I.ITEM_DESC
FROM SUPPLIERS S
INNER JOIN ITEMS I ON
(S.SUPPLIER_NO = I.SUPPLIER_NO AND S.ITEM_NO = I.ITEM_NO)
WHERE S.SUPPLIER_NO = 'AAA'
AND S.SUPPLIER_ITEM_NO = '123'

SELECT ITEM_NO, ITEM_DESC
FROM ITEMS
WHERE SUPPLIER_NO = 'AAA'
AND ITEM_NO IN (
SELECT SUPPLIER_ITEM_NO
FROM SUPPLIERS
WHERE SUPPLIER_NO = 'AAA'
AND SUPPLIER_DESC = '123'
)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-02-19 : 08:35:20

In almost all cases, a JOIN will be faster, particularly with large tables. The only time it might be more efficient is if you have a preset list of values in the IN clause, instead of a sub-select:

SELECT col1 FROM myTable WHERE col2 IN (1,2,3,4,5)

Doing this, instead of a join to a 5 row table, will reduce the I/O that has to be performed.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources