Additional query criteria in JOIN clauses...

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)

Additional query criteria in JOIN clauses...

Author

Topic

mem
Starting Member

28 Posts

Posted - 2004-07-26 : 14:58:25

Hello,

This is kinda of a n00b question, but it might help some other folks.

I've been testing different ways of writing queries with JOIN clauses and additional criteria. I was wondering if there was any performance improvements or losses with the way a JOIN was written with additional criteria. Yes, I've read the 'Additional Criteria in the JOIN Clause' article AjarnMark wrote. And I understand (I'm not an expert) how JOINs work with additional criteria.

For example:

INNER JOIN TEST
=============================================================
SELECT COLUMN1, COLUMN2
FROM TABLEA
INNER JOIN TABLEB ON TABLEA.COLUMN1 = TABLEB.COLUMN1
AND COLUMN2 = 'SOMEVALUE'
WHERE COLUMN1 = 'ANOTHERVALUE'

---VERSUS---

SELECT COLUMN1, COLUMN2
FROM TABLEA
INNER JOIN TABLEB ON TABLEA.COLUMN1 = TABLEB.COLUMN1
WHERE COLUMN1 = 'ANOTHERVALUE'
AND COLUMN2 = 'SOMEVALUE'

LEFT JOIN TEST
=============================================================
SELECT COLUMN1, COLUMN2
FROM TABLEA
LEFT JOIN TABLEB ON TABLEA.COLUMN1 = TABLEB.COLUMN1
AND COLUMN2 = 'SOMEVALUE'
WHERE COLUMN1 = 'ANOTHERVALUE'

---VERSUS---

SELECT COLUMN1, COLUMN2
FROM TABLEA
LEFT JOIN TABLEB ON TABLEA.COLUMN1 = TABLEB.COLUMN1
WHERE COLUMN1 = 'ANOTHERVALUE'
AND COLUMN2 = 'SOMEVALUE'

Ok, so if there's a ton of data I'm trying to 'sift' through does the first version of both examples work slower? Any redundancies that can be omitted in any of the versions? Open for discussion - if any :)

Thanks in advance.

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-26 : 16:10:44

It has more to do with indexing more than anything else...

There is no difference in those queries (enter someone to make me look silly....never speak in absolutes....damn I did it again...)

Brett

8-)

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-07-26 : 16:27:09

quote:
LEFT JOIN TEST
=============================================================
SELECT COLUMN1, COLUMN2
FROM TABLEA
LEFT JOIN TABLEB ON TABLEA.COLUMN1 = TABLEB.COLUMN1
AND COLUMN2 = 'SOMEVALUE'
WHERE COLUMN1 = 'ANOTHERVALUE'

---VERSUS---

SELECT COLUMN1, COLUMN2
FROM TABLEA
LEFT JOIN TABLEB ON TABLEA.COLUMN1 = TABLEB.COLUMN1
WHERE COLUMN1 = 'ANOTHERVALUE'
AND COLUMN2 = 'SOMEVALUE'

If COLUMN2 is from TABLEA there is no difference in the results of these two queries, but if COLUMN2 is from TABLEB the results will be different with the 2nd query returning fewer rows than the first.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources