OUTER JOIN and nested INNER JOIN

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)

OUTER JOIN and nested INNER JOIN

Author

Topic

Kristen
Test

22859 Posts

Posted - 2006-09-05 : 06:36:56

I'm sure I've asked this before, but I'll be blowed if I can find it

I'm trying to:

Select stuff from table T1
If there happens to be JOIN'd stuff in table T2 then select that also ...
... but ONLY IF table T2 can also be JOINED to table T3

So if T1 will join to T2 - BUT T2 cannot be joined to T3 then I will get rows for T1 but T2 & T3 columns will be NULL

I reckon the answer is:


FROM	FirstTable AS T1
	LEFT OUTER JOIN
	(
		SecondTable AS T2
		INNER JOIN ThirdTable AS T3
			ON T3.T3_ID = T2.T3_ID
	)
		ON T2.T1_ID = T1.T1_ID

but I could do with someone brighter confirming it, or giving me a correct/better solution.

Thanks

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-05 : 06:52:48

Do you have some sample data?

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2006-09-05 : 06:54:28

Its Homework, thus no sample data

Answers should comprise no more than one response - but Peso is allowed another go

Kristen

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2006-09-05 : 06:54:54

Sounds about right to me:


CREATE TABLE FirstTable ( T1_ID int PRIMARY KEY )
CREATE TABLE SecondTable ( T1_ID int NOT NULL, T3_ID int NOT NULL,
  PRIMARY KEY (T1_ID, T3_ID) )
CREATE TABLE ThirdTable ( T3_ID int PRIMARY KEY )

INSERT INTO FirstTable
SELECT 1
UNION ALL SELECT 3

INSERT INTO SecondTable
SELECT 2, 2
UNION ALL SELECT 2, 3
UNION ALL SELECT 3, 2
UNION ALL SELECT 3, 3

INSERT INTO ThirdTable
SELECT 1
UNION ALL SELECT 3

SELECT T1.T1_ID, T2.T1_ID, T2.T3_ID, T3.T3_ID
FROM	FirstTable AS T1
	LEFT OUTER JOIN
	(
		SecondTable AS T2
		INNER JOIN ThirdTable AS T3
			ON T3.T3_ID = T2.T3_ID
	)
		ON T2.T1_ID = T1.T1_ID

/*
Result is:
T1.T1_ID T2.T1_ID T2.T3_ID T3.T3_ID
1        NULL     NULL     NULL
3        3        3        3
*/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-05 : 07:02:26

Using Arnold's sample data and DDL,

SELECT		*
FROM		ThirdTable tt
INNER JOIN	SecondTable st ON st.T3_ID = tt.T3_ID
RIGHT JOIN	FirstTable ft ON ft.T1_ID = st.T1_ID

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2006-09-05 : 07:06:29

Blast, I'm going to have to try it with the Arnold Data now ...

I don't think (instinctively, that is, so could well be wrong!) that the Right Join will work as it may leave out some eligible FirstTable data ... but I'll check and report back.

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-05 : 07:23:55

[code]SELECT *
FROM FirstTable ft
LEFT JOIN SecondTable st ON st.T1_ID = ft.T1_ID
LEFT JOIN ThirdTable tt ON tt.T3_ID = st.T3_ID
WHERE st.T1_ID IS NULL
OR tt.T3_ID IS NOT NULL[/code]

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2006-09-05 : 07:26:05

OK, so I got that wrong!

Both work. I added a test data row for T1 matching T2 where T2 had NO match with T3.

And because I can't visualise these things easily I changed the column names to indicate which table they originated from, as well as which table they referred to. And I changed the data so that all T1 data is 11, 12 ... T2 data is 21, 22, ... and T3 data is 31, 32, ...

And here is the final version I ran:



CREATE TABLE FirstTable
(
	T1_ID int PRIMARY KEY
)
GO
CREATE TABLE SecondTable
(
	T2_T1_ID int NOT NULL,
	T2_T3_ID int NOT NULL,
	PRIMARY KEY
	(
		T2_T1_ID, 
		T2_T3_ID
	)
)
GO
CREATE TABLE ThirdTable
(
	T3_ID int PRIMARY KEY
)
GO

INSERT INTO FirstTable
	  SELECT [T1_ID] = 11	-- No matching T2 record
UNION ALL SELECT 13		-- Matches 2 x T2 record, one of which matches T3
UNION ALL SELECT 14		-- Matches 1 x T2 record, which does NOT match any T3

INSERT INTO SecondTable
	  SELECT [T2_T1_ID] = 12, [T3_ID] = 32	-- No matching T1 record, No matching T3 record
UNION ALL SELECT 12, 33				-- No matching T1 record, matches 1 x T3 record
UNION ALL SELECT 13, 32				-- Matches 1 x T1 record, No matching T3 record
UNION ALL SELECT 13, 33				-- Matches 1 x T1 record, matches 1 x T3 record
UNION ALL SELECT 14, 32				-- Matches 1 x T1 record, No matching T3 record

INSERT INTO ThirdTable
	  SELECT [T3_ID] = 31			-- No matching T2 record
UNION ALL SELECT 33				-- Matches 2 x T2 record, matches 1 x T1 record

-- Arnold
SELECT T1.T1_ID, T2.T2_T1_ID, T2.T2_T3_ID, T3.T3_ID
FROM	FirstTable AS T1
	LEFT OUTER JOIN
	(
		SecondTable AS T2
		INNER JOIN ThirdTable AS T3
			ON T3.T3_ID = T2.T2_T3_ID
	)
		ON T2.T2_T1_ID = T1.T1_ID
ORDER BY T1.T1_ID, T2.T2_T1_ID, T2.T2_T3_ID, T3.T3_ID

-- Peso
SELECT T1.T1_ID, T2.T2_T1_ID, T2.T2_T3_ID, T3.T3_ID
FROM	ThirdTable AS T3
	INNER JOIN SecondTable AS T2
		ON T2.T2_T3_ID = T3.T3_ID
	RIGHT JOIN FirstTable AS T1
		ON T1.T1_ID = T2.T2_T1_ID
ORDER BY T1.T1_ID, T2.T2_T1_ID, T2.T2_T3_ID, T3.T3_ID
GO

DROP TABLE FirstTable
GO
DROP TABLE SecondTable
GO
DROP TABLE ThirdTable
GO

/*
Result is:
T1_ID       T2_T1_ID    T2_T3_ID    T3_ID       
----------- ----------- ----------- ----------- 
11          NULL        NULL        NULL	-- No T2 match
13          13          33          33		-- T2 and T3 match
14          NULL        NULL        NULL	-- T2 match, but no T3 match (therefore just shows T1)
*/

Many thanks chaps,

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-05 : 07:35:54

Glad to help!

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2006-09-05 : 08:41:36

Stuck again ...

... I need to include a reference from a column in T1 to T3 and SQL says its out of scope :-(

The error is with:


SELECT T1.T1_ID, T2.T2_T1_ID, T2.T2_T3_ID, T3.T3_ID
FROM	FirstTable AS T1
	LEFT OUTER JOIN
	(
		SecondTable AS T2
		INNER JOIN ThirdTable AS T3
			 ON T3.T3_ID = T2.T2_T3_ID
			AND T3.T3_T1_ID = T1.T1_ID	-- Out of scope [:-(]
	)
		 ON T2.T2_T1_ID = T1.T1_ID
ORDER BY T1.T1_ID, T2.T2_T1_ID, T2.T2_T3_ID, T3.T3_ID

so I've moved that to be part of the LEFT OUTER JOIN to T2 thus:


SELECT T1.T1_ID, T2.T2_T1_ID, T2.T2_T3_ID, T3.T3_ID
FROM	FirstTable AS T1
	LEFT OUTER JOIN
	(
		SecondTable AS T2
		INNER JOIN ThirdTable AS T3
			 ON T3.T3_ID = T2.T2_T3_ID
	)
		 ON T2.T2_T1_ID = T1.T1_ID
		AND T3.T3_T1_ID = T1.T1_ID	-- Reverse match usage only
ORDER BY T1.T1_ID, T2.T2_T1_ID, T2.T2_T3_ID, T3.T3_ID

Please note that the actual match is on a different column in T1, it is NOT a match between T3 and the PK on table T1 !!

Peso: I can't make your routine handle this, but maybe I'm being thick?

Here's the full Sample Data, with an extra column added to T3 referencing back to T1


CREATE TABLE FirstTable
(
	T1_ID int PRIMARY KEY
)
GO
CREATE TABLE SecondTable
(
	T2_T1_ID int NOT NULL,
	T2_T3_ID int NOT NULL,
	PRIMARY KEY
	(
		T2_T1_ID, 
		T2_T3_ID
	)
)
GO
CREATE TABLE ThirdTable
(
	T3_ID int PRIMARY KEY,
	T3_T1_ID int
)
GO

INSERT INTO FirstTable
	  SELECT [T1_ID] = 11	-- No matching T2 record
UNION ALL SELECT 13		-- Matches 2 x T2 record, one of which matches T3
UNION ALL SELECT 14		-- Matches 1 x T2 record, which does NOT match any T3
UNION ALL SELECT 15		-- Matches 2 x T2 record, one of which matches T3 - but does not reverse-match

INSERT INTO SecondTable
	  SELECT [T2_T1_ID] = 12, [T3_ID] = 32	-- No matching T1 record, No matching T3 record
UNION ALL SELECT 12, 33				-- No matching T1 record, matches 1 x T3 record
UNION ALL SELECT 13, 32				-- Matches 1 x T1 record, No matching T3 record
UNION ALL SELECT 13, 33				-- Matches 1 x T1 record, matches 1 x T3 record
UNION ALL SELECT 14, 32				-- Matches 1 x T1 record, No matching T3 record
UNION ALL SELECT 15, 35				-- Matches 1 x T1 record, matches 1 x T3 record - but NOT Reverse match

INSERT INTO ThirdTable
	  SELECT [T3_ID] = 31, [T3_T1_ID] = 10	-- No matching T2 record
UNION ALL SELECT 33, 13				-- Matches 2 x T2 record, matches 1 x T1 record
UNION ALL SELECT 35, 10				-- Matches 2 x T2 record, matches 1 x T1 record

-- Arnold
SELECT T1.T1_ID, T2.T2_T1_ID, T2.T2_T3_ID, T3.T3_ID
FROM	FirstTable AS T1
	LEFT OUTER JOIN
	(
		SecondTable AS T2
		INNER JOIN ThirdTable AS T3
			 ON T3.T3_ID = T2.T2_T3_ID
	)
		 ON T2.T2_T1_ID = T1.T1_ID
		AND T3.T3_T1_ID = T1.T1_ID	-- Reverse match usage only
ORDER BY T1.T1_ID, T2.T2_T1_ID, T2.T2_T3_ID, T3.T3_ID

-- Peso (No longer handles the reverse-match from T1 to T3)
SELECT T1.T1_ID, T2.T2_T1_ID, T2.T2_T3_ID, T3.T3_ID
FROM	ThirdTable AS T3
	INNER JOIN SecondTable AS T2
		 ON T2.T2_T3_ID = T3.T3_ID
	RIGHT JOIN FirstTable AS T1
		ON T1.T1_ID = T2.T2_T1_ID
ORDER BY T1.T1_ID, T2.T2_T1_ID, T2.T2_T3_ID, T3.T3_ID
GO

DROP TABLE FirstTable
GO
DROP TABLE SecondTable
GO
DROP TABLE ThirdTable
GO

/*
Result is:
T1_ID       T2_T1_ID    T2_T3_ID    T3_ID       
----------- ----------- ----------- ----------- 
11          NULL        NULL        NULL	-- No T2 match
13          13          33          33		-- T2 and T3 match
14          NULL        NULL        NULL	-- T2 match, but no T3 match (therefore just shows T1)
15          NULL        NULL        NULL	-- T2 and T3 match, but T3 does not Reverse match T1
*/

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-05 : 08:48:09

[code]SELECT T1.T1_ID, T2.T2_T1_ID, T2.T2_T3_ID, T3.T3_ID
FROM ThirdTable AS T3
INNER JOIN SecondTable AS T2
ON T2.T2_T3_ID = T3.T3_ID
RIGHT JOIN FirstTable AS T1
ON T1.T1_ID = T2.T2_T1_ID AND T1.T1_ID = T3.T3_T1_ID
ORDER BY T1.T1_ID, T2.T2_T1_ID, T2.T2_T3_ID, T3.T3_ID[/code]

Peter Larsson
Helsingborg, Sweden

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-09-05 : 09:05:07

Stay away from RIGHT JOINS -- they make your SQL very hard to interpret and are always written more clearly as LEFT JOINS.

As for the original issue, don't nest your joins, nest some derived tables. Again, it makes things easier to read and also solves your "out of scope" issues:


SELECT T1.T1_ID, x.T2_T1_ID, x.T2_T3_ID, x.T3_ID
FROM	FirstTable AS T1
LEFT OUTER JOIN
	(
                SELECT T2.t2_t1_id, t2.t2_t3_id, t3.t3_id
                FROM
		SecondTable AS T2
		INNER JOIN ThirdTable AS T3
			 ON T3.T3_ID = T2.T2_T3_ID
			AND T3.T3_T1_ID = T1.T1_ID	
	) x
		 ON x.T2_T1_ID = T1.T1_ID 
ORDER BY T1.T1_ID, x.T2_T1_ID, x.T2_T3_ID, x.T3_ID

(I might have missed something in there, can't test right now, but you should get the idea).

Derived tables are pretty much always preferred to nested JOINS expressions, IMHO, for readability and the fact that you get a nice, clear, mini-SELECT within the bigger select that you can analyze, troubleshoot individually, and so on.

- Jeff

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-05 : 09:16:15

Why is RIGHT join harder to read and understand than LEFT join?

It's only a matter of what you got and what you want.

DECLARE	@Test1 TABLE (ID INT)

INSERT	@Test1
SELECT	1 UNION ALL
SELECT	2 UNION ALL
SELECT	3 UNION ALL
SELECT	4 UNION ALL
SELECT	5

DECLARE	@Test2 TABLE (ID INT)

INSERT	@Test2
SELECT	1 UNION ALL
SELECT	3 UNION ALL
SELECT	5


Normally we use LEFT JOIN to get all values from Table1 and the values from Table2 that matches.

SELECT		t1.*,
		t2.*
FROM		@Test1 t1
LEFT JOIN	@Test2 t2 ON t2.ID = t1.ID

But there is no difference in using this.

SELECT		t1.*,
		t2.*
FROM		@Test2 t2
RIGHT JOIN	@Test1 t1 ON t1.ID = t2.ID

Think of "what I have when I start" and "what I have when I end".
or think of "what I have when I start" and "what I am adding".

Peter Larsson
Helsingborg, Sweden

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-09-05 : 09:31:11

RIGHT JOINS don't read well and they are hard to maintain.

select * from A left outer join B

reads: "give me all the rows from A as the primary starting point. For each row in A, also join to B and return any rows that happen to match."

Which to make makes logical sense. The FROM clause indicates the primary, driving source of the SELECT. The JOINS are to auxiliary tables. The SQL statement translates very well into English and makes logical sense.

Let's consider writing the same query as a RIGHT OUTER JOIN:

select * from B right outer join A

This reads the same, of course, since the two statements are equivalent: "give me all rows from A as the primary starting point. For each row in A, join to to B and return any rows that happen to match."

That doesn't make sense. Why then, are we selecting FROM B if we just said that we are really selecting from A? Why join to A, when we really are joing from A to B?

I fully agree that they are equivalent, and that is all the more reason to never use RIGHT OUTER JOINS. they can always be written more clearly and will always make more logical sense when written a LEFT JOINS. Plus, adding additional tables makes more logical sense as well: Add an outer join from table A to table C in both examples. the first one makes it easy and it still reads very clearly:

select *
from a
left outer join B on a = b
left outer join C on a = c

Very clear that a is the primary driving rowset, and we have to auxiliary joins to B and C. At a glance, we immediately know what is going on, and we can easily add further tables to this and still have it read logically.

select *
from b
right outer join a on a=b
left outer join c on a=c

Does that make sense to you? is it clear and easy to read? I don't even know how you would write that with two RIGHT OUTER JOINS !

- Jeff

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-05 : 09:47:42

For me, it does.

select		*
from		c
right join	b on b = c
right join	a on a = b

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2006-09-05 : 10:17:23

Arnold's Nested Select

Darn it Arnold! I came in on this one because I couldn't remember the syntax well enough to be sure I would get the dataset I needed in my result. That's because I use this syntax once every couple of years at best ... whereas I write a Nested Join 3 times a week ... Why didn't I think of that first?

Here is yours (fixed a typo-thingie, as you surmised):


SELECT T1.T1_ID, x.T2_T1_ID, x.T2_T3_ID, x.T3_ID
FROM	FirstTable AS T1
	LEFT OUTER JOIN
	(
                SELECT	T2.T2_T1_ID, T2.T2_T3_ID, T3.T3_ID, T3.T3_T1_ID
                FROM	SecondTable AS T2
			JOIN ThirdTable AS T3
				ON T3.T3_ID = T2.T2_T3_ID
	) AS x
		 ON x.T2_T1_ID = T1.T1_ID 
		AND x.T3_T1_ID = T1.T1_ID
ORDER BY T1.T1_ID, x.T2_T1_ID, x.T2_T3_ID, x.T3_ID

However, question: Is the nested statement:


	(
                SELECT T2.t2_t1_id, t2.t2_t3_id, t3.t3_id
                FROM
		SecondTable AS T2
		INNER JOIN ThirdTable AS T3
			 ON T3.T3_ID = T2.T2_T3_ID
	) x

inefficient because it is, in effect, pulling all rows that match, rather than just the ones that would satisfy the outer-loop's Join - or is SQL's optimiser smart enough to prevent that?

I'm pretty sure in such instances before I've wound up putting an addition Join, within the nested select, further constraining the inner-loop's Select with a JOIN to the outer-loop's table - to reduce the number of rows it can see - for example:


SELECT T1.T1_ID, x.T2_T1_ID, x.T2_T3_ID, x.T3_ID
FROM	FirstTable AS T1
	LEFT OUTER JOIN
	(
                SELECT	T2.T2_T1_ID, T2.T2_T3_ID, T3.T3_ID, T3.T3_T1_ID
                FROM	SecondTable AS T2
			JOIN ThirdTable AS T3
				 ON T3.T3_ID = T2.T2_T3_ID
			JOIN FirstTable AS T1b
				 ON T1b.T1_ID = T3.T3_T1_ID
				AND T1b.T1_ID = T2.T2_T1_ID
	) AS x
		 ON x.T2_T1_ID = T1.T1_ID 
		AND x.T3_T1_ID = T1.T1_ID
ORDER BY T1.T1_ID, x.T2_T1_ID, x.T2_T3_ID, x.T3_ID

Peso's RIGHT JOIN discussion

I would struggle with that in my code - i.e. I would not instinctively react to what it was doing, and therefore might spend longer than I should diagnosing a problem. My coding-style is very much centred around "defensive programming" to reduce time in maintenance downstream - so to me, at least, a RIGHT JOIN is a "Risk"

Kristen

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-09-05 : 10:53:59

quote:
Originally posted by Peso

For me, it does.
select *
from c
right join b on b = c
right join a on a = b

Peter Larsson
Helsingborg, Sweden

You just made my point! Looking at that, I can't even figure out which table -- a,b or c -- would have ALL rows returned and which ones are auxiliary tables that are being outer joined to. Also, due to the confusion with RIGHT OUTER JOINS, someone who is knowledgeable in SQL such as yourself didn't even write it correctly; it doens't return the same results:


create table a (aID int primary key)
insert into a
select 1 union all
select 2 union all
select 3 union all
select 4
go


create table b (bID int primary key)
insert into b
select 2 union all
select 3

go

create table c (cID int primary key)
insert into c
select 3 union all
select 4


go


select aID,bID,cID
from a
left outer join b on aID = bID
left outer join c on aID = cID

select aID,bID,cID
from		c
right join	b on bID = cID
right join	a on aID = bID

go

returns:


aID         bID         cID         
----------- ----------- ----------- 
1           NULL        NULL
2           2           NULL
3           3           3
4           NULL        4

(4 row(s) affected)

aID         bID         cID         
----------- ----------- ----------- 
1           NULL        NULL
2           2           NULL
3           3           3
4           NULL        NULL

(4 row(s) affected)

In fact, I don't even think it is possible to write it with two RIGHT OUTER JOINS since it would need to be parsed backwards; i.e., this will not compile or execute:


select aID,bID,cID
from		c
right join	b on aID = bID
right join	a on aID = cID

Which I *think* would be the actual equivalent.

Seriously, can you really honestly tell me that RIGHT OUTER JOINS are just as readable as LEFT OUTER JOINS?

Whether your 2 RIGHT OUTER JOIN SELECT is the equivalent or not, can you translate it into English for me? Does that in any way line up with how it is written?

- Jeff

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-09-05 : 10:59:27

quote:
Originally posted by Kristen

Arnold's Nested Select

Darn it Arnold! I came in on this one because I couldn't remember the syntax well enough to be sure I would get the dataset I needed in my result. That's because I use this syntax once every couple of years at best ... whereas I write a Nested Join 3 times a week ... Why didn't I think of that first?

Jeff suggested the nested SELECT, not Arnold .

yes, the optimizer should be smart enough to join only the matching rows in the derived table. You should not have to add more to it as in your second example. derived tables can and should be used often to simplify your SELECT and to break it up into smaller, more manageable pieces.

- Jeff

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-05 : 11:22:33

No, I would prefer LEFT JOIN beacuse that's the way I am used to read, but in the original posting, a right join with an inner join was easier to read for me. And I didn't have the need for a derived table neither.

The double LEFT JOIN can't be written with RIGHT JOIN only. A FULL JOIN and a RIGHT JOIN does the trick however

select aID,bID,cID
from		c
full join	b on bID = cID
right join	a on aID = bID

But that is not the point. The point here is that RIGHT JOIN and LEFT JOIN are not equivalent. I can ask you the same thing with the samples you provided above, how do you produce this following resultset with only LEFT JOINs?

aID	bID	cID
---	---	---
3	3	3
NULL	NULL	4

I don't tell people to use RIGHT JOIN just because I think it is fun, or in favor of LEFT JOIN.

Different solutions has different needs.

Peter Larsson
Helsingborg, Sweden

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-09-05 : 11:43:57

quote:
Originally posted by Peso

No, I would prefer LEFT JOIN beacuse that's the way I am used to read, but in the original posting, a right join with an inner join was easier to read for me. And I didn't have the need for a derived table neither.

The double LEFT JOIN can't be written with RIGHT JOIN only. A FULL JOIN and a RIGHT JOIN does the trick however
select aID,bID,cID
from c
full join b on bID = cID
right join a on aID = bID
But that is not the point. The point here is that RIGHT JOIN and LEFT JOIN are not equivalent. I can ask you the same thing with the samples you provided above, how do you produce this following resultset with only LEFT JOINs?
aID bID cID
--- --- ---
3 3 3
NULL NULL 4

select from c
left outer join a on a= c
left outer join b on b=c

very simple, very clear, makes perfect sense and you immediately know that the "driving" table is c and auxillary tables that being joined to are a and b.

quote:

I don't tell people to use RIGHT JOIN just because I think it is fun, or in favor of LEFT JOIN.

Different solutions has different needs.

The point I am making it not about equivalency, it is about clarity and simplicity and ease of maintenance.

I will agree that different solutions have different needs, but NO solution requires a RIGHT OUTER JOIN. It is always written more clearly as a LEFT OUTER JOIN, and as we saw, in many cases it cannot be written with all RIGHT joins (you need to bring even worse things into the SELECT such as FULL joins) when it can be written very simply and clearly as a couple of LEFT joins.

As i mentioned a few times, you'd see this for yourself if you'd try to translate something like this:

select aID,bID,cID
from c
full join b on bID = cID
right join a on aID = bID

into a simple, clear sentence that describes what this SELECT is doing.

- Jeff

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-05 : 11:54:30

So you think the result

aID	bID	cID
---	---	---
3	3	3
4	4	NULL

from

select from c
left outer join a on a= c
left outer join b on b=c

look the same?

Peter Larsson
Helsingborg, Sweden

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources