INNER JOIN versus JOIN

Question

Post reply

INNER JOIN versus JOIN

Farrell Keough

SSCoach

Points: 17414
More actions
May 23, 2005 at 1:54 pm

#76319

I have tried looking this up on BOL as well as this site and Google. I must be asking the wrong question - that is the important thing in life, isn't it?

I cannot seem to generate different results when I use INNER JOIN versus JOIN. Is INNER an artifact the older OUTER JOIN terminology? Has someone run into an instance where different results were achieved using JOIN rather than INNER JOIN?

Thanks.
I wasn't born stupid - I had to study.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

INNER JOIN is the explicit version of JOIN. By DEFAULT if no join type is given SQL says INNER JOIN...

Good Hunting!

AJ Ahrens

webmaster@kritter.net

Farrell Keough SSCoach Points: 17414 More actions · Answer 2

Thanks. I figured that, but I could find no documentation.

I wasn't born stupid - I had to study.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

Farrell,

AJ already mentioned that INNER JOIN is the default JOIN type. Try this

USE PUBS

SELECT

r.royalty

, t.title

, t.type

, t.price

FROM

roysched r

INNER JOIN

titles t

ON

r.title_id = t.title_id

ORDER BY

r.royalty

SELECT

r.royalty

, t.title

, t.type

, t.price

FROM

roysched r

JOIN

titles t

ON

r.title_id = t.title_id

ORDER BY

r.royalty

SELECT

r.royalty

, t.title

, t.type

, t.price

FROM

roysched r

MERGE JOIN

titles t

ON

r.title_id = t.title_id

ORDER BY

r.royalty

and you'll see, why it is preferrable to be explicite in the JOIN type you want to use. I think I've also read this in BOL, but can't remember where right now.

...If you ask me, I think it's one of those little SQL Server annoyances.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

ron k SSCertifiable Points: 6103 More actions · Answer 4

From Books online (From Clause):

< join_type >

Specifies the type of join operation.

INNER

Specifies that all matching pairs of rows are returned. Discards unmatched rows from both tables. This is the default if no join type is specified.

LEFT [ OUTER ]

Specifies that all rows from the left table not meeting the specified condition are included in the result set in addition to all rows returned by the inner join. Output columns from the left table are set to NULL.

RIGHT [ OUTER ]

Specifies that all rows from the right table not meeting the specified condition are included in the result set in addition to all rows returned by the inner join. Output columns from the right table are set to NULL.

FULL [ OUTER ]

If a row from either the left or right table does not match the selection criteria, specifies the row be included in the result set, and output columns that correspond to the other table be set to NULL. This is in addition to all rows usually returned by the inner join.

Farrell Keough SSCoach Points: 17414 More actions · Answer 5

Thanks Ron. I had seen that, but it just wasn't specific enough for me as to whether it would make a difference in the results.

---------------------------------------------------------------------------------

Frank, I had to use different tables.

I get an error using MERGE JOIN. Can you see what I have done wrong?

SELECT S.STATE_CODE, P.STATE_CODE, P.EMAIL_ADDR, PERSON_ID

FROM PERSON_ADDRESSES P

MERGE JOIN STATES S ON( P.STATE_CODE = S.STATE_CODE)

ORDER BY P.EMAIL_ADDR -- x records

I wasn't born stupid - I had to study.

ron k SSCertifiable Points: 6103 More actions · Answer 6

Merge Join is a Join HINT not a join type.

From books online:

H. Use HASH and MERGE join hints

This example performs a three-table join among the authors, titleauthors,

and titles tables to produce a list of authors and the books they have written.

The query optimizer joins authors and titleauthors (A x TA) using a MERGE join.

Next, the results of the authors and titleauthors MERGE join (A x TA) are

HASH joined with the titles table to produce (A x TA) x T.

Important After a join hint is specified, the INNER keyword is no longer optional and

must be explicitly stated for an INNER JOIN to be performed.

USE pubs
SELECT SUBSTRING((RTRIM(a.au_fname)
+ ' ' + LTRIM(a.au_lname)), 1, 25)   AS Name, 
SUBSTRING(t.title, 1, 20) AS Title
FROM authors a INNER MERGE JOIN titleauthor ta    
ON a.au_id = ta.au_id INNER HASH JOIN titles t    
ON t.title_id = ta.title_id
ORDER BY au_lname ASC, au_fname ASC

Frank Kalis SSC Guru Points: 111183 More actions · Answer 7

Farrell, you did nothing wrong. That's exactly what I meant with little annoaynce. Not that one should specify JOINs hints as normal way of doing business, but when you specify a JOIN hint, you also need to write INNER. Obviously SQL Server "forgets" about INNER JOIN being the default in such cases.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Farrell Keough SSCoach Points: 17414 More actions · Answer 8

Thank you guys! I was born with an extra thick skull, so it usually takes two or three hammers to get me to understand.

I wasn't born stupid - I had to study.