Sign in to follow this  
plywood

SQL Join issues

Recommended Posts

*Using SQL Server 2005* I am having a difficult time finding the answer to these questions on google. I have two tables, Players and Ogres. I want to pull hybrid data out of the two tables by joining them together. Below is a "generic query" that I am using:
SELECT Players.PLAYER_NAME, Players.PLAYER_SKILL, Ogres.OGRE_EVILNESS
FROM Players
JOIN_TYPE Ogres
ON Players.PLAYER_ID = Ogres.OGRE_ID

...where JOIN_TYPE can be any of the following: (1) INNER JOIN (or just JOIN, as all joins are INNER by default in SQL Server) (2) LEFT JOIN (3) RIGHT JOIN (4) FULL JOIN (5) OUTER JOIN (6) LEFT OUTER JOIN (7) RIGHT OUTER JOIN (8) FULL OUTER JOIN When I run the generic query above 8 times - once for each JOIN_TYPE - I get some interesting results, which have led to two interesting questions: (1) I get a syntax error for OUTER JOIN - is there no such thing as a "pure" OUTER JOIN in SQL Server Land? Meaning, does one *have* to qualify an outer join as being LEFT, RIGHT or FULL? (2) I am seeing identical result sets in certain pairs of inner and outer joins. Is this *always* the case? For instance, will, say, a LEFT INNER JOIN always return the same result set as, perhaps, a RIGHT OUTER JOIN? Even if that specific example is false, I am talking about the existence of a symmetry in the INNER/OUTER and/or LEFT/RIGHT join mechanisms in general.

Share this post


Link to post
Share on other sites

Table1 Table2

Table1ID Name Table2ID Table1ID Game
-------- ---- -------- -------- ----
1 Joe 1 1 Gears of War
2 1 Halo 3
2 Bill 3 2 Minesweeper
3 Mary
4 NULL Flight Sim



The above data example will help demonstrate the purpose of each join for the
following select statement:


SELECT
t1.Table1ID,
t1.Name,
t2.Table2ID,
t2.Game
FROM
...





INNER JOIN
----------

Syntax: Table1 t1 INNER JOIN Table2 t2 ON t1.Table1ID = t2.Table1ID

Result: When I inner join table1 and table 2, records from either table will
ONLY be returned in the resultset if they CAN be joined with records from the
other table acording to the join criteria. SO, record 3 (Mary) from Table1
and record 4 (Flight Sim) from Table2 WILL NOT be included in the resultset
because there is no matching record(s) in other table.


Table1ID Name Table2ID Game
-------- ---- -------- ----
1 Joe 1 Gears of War
1 Joe 2 Halo3
2 Bill 3 Minesweeper




LEFT OUTER JOIN
---------------

Syntax: Table1 t1 LEFT OUTER JOIN Table2 t2 ON t1.Table1ID = t2.Table1ID

Result: When I left outer join table1 and table 2, records from the LEFT table
(Table1) will ALWAYS be returned in the resultset even if they CAN'T be joined
with records from the RIGHT table acording to the join criteria. SO, record 3
(Mary) from Table1 WILL be included in the resultset even though there is no
matching record(s) in Table 2, but record 4 (Flight Sim) from Table2 WILL NOT
be included in the resultset because there is no matching record in Table 1.


Table1ID Name Table2ID Game
-------- ---- -------- ----
1 Joe 1 Gears of War
1 Joe 2 Halo3
2 Bill 3 Minesweeper
3 Mary NULL NULL




RIGHT OUTER JOIN
----------------

Syntax: Table1 t1 RIGHT OUTER JOIN Table2 t2 ON t1.Table1ID = t2.Table1ID

Result: When I right outer join table1 and table 2, records from the RIGHT
table (Table2) will ALWAYS be returned in the resultset even if they CAN'T be
joined with records from the LEFT table acording to the join criteria. SO,
record 4 (Flight Sim) from Table2 WILL be included in the resultset even
though there is no matching record(s) in Table 1, but record 3 (Mary) from
Table1 WILL NOT be included in the resultset because there is no matching
record in Table 2.


Table1ID Name Table2ID Game
-------- ---- -------- ----
1 Joe 1 Gears of War
1 Joe 2 Halo3
2 Bill 3 Minesweeper
NULL NULL 4 Flight Sim




FULL OUTER JOIN
---------------

Syntax: Table1 t1 FUL OUTER JOIN Table2 t2 ON t1.Table1ID = t2.Table1ID

Result: When I full outer join table1 and table 2, records from the LEFT &
RIGHT table will ALWAYS be returned in the resultset even if they CAN'T be
joined with records from the other table acording to the join criteria. SO,
record 4 (Flight Sim) from Table2 WILL be included in the resultset even
though there is no matching record(s) in Table 1, AND record 3 (Mary) from
Table1 WILL be included in the resultset event though there is no matching
record in Table 2.



Table1ID Name Table2ID Game
-------- ---- -------- ----
1 Joe 1 Gears of War
1 Joe 2 Halo3
2 Bill 3 Minesweeper
3 Mary NULL NULL
NULL NULL 4 Flight Sim




Hope this help you understand why data may or may not be returned in a resultset based on which join type is used!

[Edited by - Plasmana on October 13, 2008 10:23:51 PM]

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this