Join

Question

Post reply

Join

snadsul

SSC Rookie

Points: 25
More actions
May 21, 2012 at 5:44 am

#261474

What is diff betn join and left join .Plz explain with example

Viewing 4 posts - 1 through 3 (of 3 total)

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

Gullimeel Hall of Fame Points: 3871 More actions · Answer 1

This is from BOL.It has nice explaination of joins.

http://msdn.microsoft.com/en-us/library/ms191472(v=sql.100).aspx

GulliMeel

Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 2

Agreed, BOL will answer this for you.

However...

CREATE TABLE firstTable (uid INT, data VARCHAR(3) )

CREATE TABLE secondTable (uid INT, data VARCHAR(3) )

INSERT INTO firstTable VALUES (1, 'abc')

INSERT INTO firstTable VALUES (2, 'bcd')

INSERT INTO firstTable VALUES (3, 'cde')

INSERT INTO secondTable VALUES (1, 'abc')

INSERT INTO secondTable VALUES (3, 'bcd')

INSERT INTO secondTable VALUES (4, 'cde')

SELECT ft.uid, ft.data FROM firstTable ft INNER JOIN secondTable st ON ft.uid = st.uid

Will return:

1, 'abc'

3, 'cde'

SELECT ft.uid, ft.data FROM firstTable ft LEFT JOIN secondTable st ON ft.uid = st.uid

Will return:

1, 'abc'

2, 'bcd'

3, 'cde'

Because it returns what the INNER JOIN does plus any NULL returns on the right side of the join.

SELECT ft.uid, ft.data FROM firstTable ft RIGHT JOIN secondTable st ON ft.uid = st.uid

1, 'abc'

3, 'cde'

NULL, NULL

Because it returns what the INNER JOIN does plus any NULL returns on the left side of the join.

SELECT ft.uid, ft.data FROM firstTable ft CROSS JOIN secondTable st

1, 'abc'

2, 'bcd'

3, 'cde'

1, 'abc'

2, 'bcd'

3, 'cde'

1, 'abc'

2, 'bcd'

3, 'cde'

Will return all possible results (3^3 in this case) of all possible JOINs.

For more information review the previous BOL link.

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 3

have a look at this link, it shows you the joins in picture form too

http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins

***The first step is always the hardest *******