RIGHT OUTER JOIN - SQL Server Forums

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)

RIGHT OUTER JOIN

Author

Topic

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-04-01 : 13:29:47

Alright, so I've got two solutions already, but I don't like either. The first working solution just doesn't seem right with the derived table. The second solution uses a RIGHT OUTER JOIN, ick.

What I want to see is a row per driver regardless if they are in a group or not. xgrDriver links the two tables together.



SET NOCOUNT ON

DECLARE @Driver table (DriverId int, DriverName varchar(20))
DECLARE @DriverGroup table (GroupId int, GroupName varchar(20))
DECLARE @xgrDriver table (GroupId int, DriverId int)


INSERT INTO @Driver VALUES(1,'Driver1')
INSERT INTO @Driver VALUES(2,'Driver2')
INSERT INTO @Driver VALUES(3,'Driver3')

INSERT INTO @DriverGroup VALUES(1,'Group1')
INSERT INTO @DriverGroup VALUES(2,'Group2')

INSERT INTO @xgrDriver VALUES(1, 1)
INSERT INTO @xgrDriver VALUES(1, 2)
INSERT INTO @xgrDriver VALUES(2, 1)

-- this doesn't work
SELECT dg.GroupName, dr.DriverName
FROM @Driver dr
LEFT OUTER JOIN @xgrDriver xd
ON dr.DriverId = xd.DriverId
INNER JOIN @DriverGroup dg
ON xd.GroupId = dg.GroupId
ORDER BY dg.GroupName, dr.DriverName

-- this works but do we really need the derived table?
SELECT g.GroupName, dr.DriverName
FROM @Driver dr
LEFT OUTER JOIN 
(
	SELECT dg.GroupName, xd.DriverId
	FROM @xgrDriver xd
	INNER JOIN @DriverGroup dg
	ON xd.GroupId = dg.GroupId
) g
ON dr.DriverId = g.DriverId
ORDER BY g.GroupName, dr.DriverName

-- this works but it's using RIGHT OUTER JOIN, which can be confusing as Jeff has pointed out
SELECT dg.GroupName, dr.DriverName
FROM @xgrDriver xd
INNER JOIN @DriverGroup dg
ON xd.GroupId = dg.GroupId
RIGHT OUTER JOIN @Driver dr
ON xd.DriverId = dr.DriverId
ORDER BY dg.GroupName, dr.DriverName

The non-working solution doesn't output Driver3. The other two working solutions have the correct result set, but is there a better way?

Tara

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-04-01 : 13:38:23

This?

SELECT dg.GroupName, dr.DriverName
FROM @Driver dr
LEFT OUTER JOIN
(
	@xgrDriver xd
	INNER JOIN @DriverGroup dg
		ON xd.GroupId = dg.GroupId
)
ON dr.DriverId = xd.DriverId
ORDER BY dg.GroupName, dr.DriverName

rockmoose

rfrancisco
Yak Posting Veteran

95 Posts

Posted - 2005-04-01 : 13:38:32

Try this:

SELECT dg.GroupName, dr.DriverName
FROM (@Driver dr
LEFT OUTER JOIN @xgrDriver xd
ON dr.DriverId = xd.DriverId)
LEFT OUTER JOIN @DriverGroup dg
ON xd.GroupId = dg.GroupId
ORDER BY dg.GroupName, dr.DriverName

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-04-01 : 13:40:04

quote:

Originally posted by rockmoose

This?

SELECT dg.GroupName, dr.DriverName
FROM @Driver dr
LEFT OUTER JOIN
(
	@xgrDriver xd
	INNER JOIN @DriverGroup dg
		ON xd.GroupId = dg.GroupId
)
ON dr.DriverId = xd.DriverId
ORDER BY dg.GroupName, dr.DriverName

rockmoose

That's what I have in my original post as the working solution that doesn't seem right with the derived table.

Tara

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-04-01 : 13:41:38

>> That's what I have in my original post as the working solution that doesn't seem right with the derived table.
No, it is not, there is no derived table in the solution I posted.

Let me rewrite without ()

SELECT dg.GroupName, dr.DriverName
FROM @Driver dr
LEFT OUTER JOIN @xgrDriver xd
	INNER JOIN @DriverGroup dg
		ON xd.GroupId = dg.GroupId
	ON dr.DriverId = xd.DriverId
ORDER BY dg.GroupName, dr.DriverName

rockmoose

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-04-01 : 13:43:06

quote:
Originally posted by rfrancisco

Try this:

SELECT dg.GroupName, dr.DriverName
FROM (@Driver dr
LEFT OUTER JOIN @xgrDriver xd
ON dr.DriverId = xd.DriverId)
LEFT OUTER JOIN @DriverGroup dg
ON xd.GroupId = dg.GroupId
ORDER BY dg.GroupName, dr.DriverName

I had used that in the past, but a LEFT OUTER JOIN to @DriverGroup doesn't seem right. Yes it produces the correct result set, but LEFT OUTER JOIN to @DriverGroup just doesn't make sense since we want the matches between @xgrDriver and @DriverGroup.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-04-01 : 13:46:28

quote:
Originally posted by rockmoose

>> That's what I have in my original post as the working solution that doesn't seem right with the derived table.
No, it is not, there is no derived table in the solution I posted.

Let me rewrite without ()
SELECT dg.GroupName, dr.DriverName
FROM @Driver dr
LEFT OUTER JOIN @xgrDriver xd
INNER JOIN @DriverGroup dg
ON xd.GroupId = dg.GroupId
ON dr.DriverId = xd.DriverId
ORDER BY dg.GroupName, dr.DriverName

rockmoose

I see the difference now, but I'm confused as to syntax. I always thought it was a mistake when the ON conditions were all grouped together. I can't see the difference between my non-working solution and your working solution. I mean, I can see that yours produces the correct result set, however I don't understand why. I don't get how moving the ON condition further down fixes it. I also didn't realized it changed the results.

Tara

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-04-01 : 14:09:31

If You look up "FROM" in BOL, You can see what is being done;

SELECT dg.GroupName, dr.DriverName
FROM @Driver dr
LEFT OUTER JOIN(<joined_table>)
ON dr.DriverId = xd.DriverId

SELECT dg.GroupName, dr.DriverName
FROM @Driver dr
LEFT OUTER JOIN(@xgrDriver xd INNER JOIN @DriverGroup dg ON xd.GroupId = dg.GroupId)
ON dr.DriverId = xd.DriverId

I have made some major performance improvements with this technique
applied to "complex" joins involving "large" tables. Where initially there was
a lot of LEFT JOINS, derived tables etc..

rockmoose

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-04-01 : 14:09:40

Tara -- Don't feel you need to try to avoid dervied tables. IMHO, they actually make your SQL more readable, and can often make it much more efficient. The nice advantage is that a SELECT with a few derived tables allows you to dissect the problem into different parts, troubleshoot and test each part, and then join them all together in the end.

A classic example is something like this:


SELECT C.ID, C.Name, C.Address, C.City, C.State, C.ZIP, SUM(S.Amount) as TotalAmount
FROM Customers C 
INNER JOIN Sales S
  ON C.ID = S.CustomerID
GROUP BY C.ID, C.Name, C.Address, C.City, C.State, C.ZIP

Why are we grouping on all those columns? The best way to write this query is to break it down into 2 parts, and use a derived table:


SELECT C.ID, C.Name, C.Address, C.City, C.State, C.ZIP, SalesPerCust.TotalAmount
FROM Customers C 
INNER JOIN
 (SELECT CustomerID, SUM(Amount) as TotalAmount FROM Sales GROUP BY CustomerID) SalesPerCust
ON
 C.ID = SalesPerCust.CustomerID

The second solution, in my opinion, is more readable and it is more clear what is going on. It also is more efficient, since less rows are being joined and less GROUP BY's need to be calculated.

- Jeff

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-04-01 : 15:16:04

It's not that I'm trying to avoid derived tables. I use them quite a bit actually, especially in cases where you have shown. The reason why I was questioning it though is that my actual query is several derived tables deep now. I think I'm up to 4 on one of them. These queries are being used for reports and are getting rather complex due to the requirements. I don't typically do development work, so at some point, the actual developers may need to take this over, so I'm trying to make the transition as easy as possible. I just might need to spend a little extra time with them discussing derived tables as I don't believe that they are familiar with them.

Anyway, enough of that...I'd like to discuss the difference between rockmoose's solution and the derived table solution that I posted. They seem to be the same now that I've seen rockmoose's explanation. I've rewritten the code to use temp tables so that we could get a composite index on xgrDriver. The execution plans are identical between the RIGHT OUTER JOIN, derived table, and rockmoose's solution.



SET NOCOUNT ON

CREATE TABLE #Driver (DriverId int PRIMARY KEY CLUSTERED, DriverName varchar(20))
CREATE TABLE #DriverGroup (GroupId int PRIMARY KEY CLUSTERED, GroupName varchar(20))
CREATE TABLE #xgrDriver (GroupId int , DriverId int, PRIMARY KEY CLUSTERED(GroupId, DriverId))


INSERT INTO #Driver VALUES(1,'Driver1')
INSERT INTO #Driver VALUES(2,'Driver2')
INSERT INTO #Driver VALUES(3,'Driver3')

INSERT INTO #DriverGroup VALUES(1,'Group1')
INSERT INTO #DriverGroup VALUES(2,'Group2')

INSERT INTO #xgrDriver VALUES(1, 1)
INSERT INTO #xgrDriver VALUES(1, 2)
INSERT INTO #xgrDriver VALUES(2, 1)

-- this works but do we really need the derived table?
SELECT g.GroupName, dr.DriverName
FROM #Driver dr
LEFT OUTER JOIN 
(
	SELECT dg.GroupName, xd.DriverId
	FROM #xgrDriver xd
	INNER JOIN #DriverGroup dg
	ON xd.GroupId = dg.GroupId
) g
ON dr.DriverId = g.DriverId
ORDER BY g.GroupName, dr.DriverName

-- this works but it's using RIGHT OUTER JOIN, which can be confusing as Jeff has pointed out
SELECT dg.GroupName, dr.DriverName
FROM #xgrDriver xd
INNER JOIN #DriverGroup dg
ON xd.GroupId = dg.GroupId
RIGHT OUTER JOIN #Driver dr
ON xd.DriverId = dr.DriverId
ORDER BY dg.GroupName, dr.DriverName

-- rockmoose's solution
SELECT dg.GroupName, dr.DriverName
FROM #Driver dr
LEFT OUTER JOIN #xgrDriver xd
	INNER JOIN #DriverGroup dg
		ON xd.GroupId = dg.GroupId
	ON dr.DriverId = xd.DriverId
ORDER BY dg.GroupName, dr.DriverName

DROP TABLE #xgrDriver, #Driver, #DriverGroup

Any comments?

Tara

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-04-01 : 15:38:22

As always, especialyl when the execution plans are the same, go with what you feel is easier to understand. I prefer the derived tables, even at 4 levels deep, but that's just me. And, do not forget that you can replace almost any derived table with a View, espcially those that are used in several different places. That can really help when it comes to making your code more mangeable.

- Jeff

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-04-01 : 15:39:49

Thanks Jeff.

Tara

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-04-01 : 16:18:50

I did find in some cases that the "nested join" approach made the query optimizer use indexes better.
( thus the performance gain I stated earlier ).
-- but as always, here we have to analyze the plan from case to case etc...

As for readability and such, I think that the derived table approach is "friendlier" and easier to understand.

Other technicalities are:
In the nested join approach all columns will be available in the select list,
not only the ones that are specified in the derived table's select lists.
( This is a non-issue, but i thought I'd mention it )

Here is another example that You might enjoy comparing the execution plans (they are the same)

CREATE TABLE A(x int); INSERT A SELECT 1
CREATE TABLE B(x int); INSERT B SELECT 1
CREATE TABLE C(x int); INSERT C SELECT 1
CREATE TABLE D(x int); INSERT D SELECT 1
CREATE TABLE E(x int); INSERT E SELECT 2
CREATE TABLE F(x int); INSERT F SELECT 2
GO

SELECT *
FROM
	A
	LEFT JOIN B
		JOIN C
			ON B.x = C.x
		ON A.x = B.x
	LEFT JOIN D
		JOIN E
			JOIN F
				ON E.x = F.x
			ON D.x = E.x
		ON A.x = D.x

SELECT *
FROM
	A
	LEFT JOIN( 	SELECT B.x, C.x cx
			FROM B JOIN C ON B.x = C.x ) d1
		ON A.x = d1.x
	LEFT JOIN( 	SELECT D.x, d3.x ex, d3.fx
			FROM D JOIN(	SELECT E.x, F.x fx
					FROM E JOIN F ON E.x = F.x) d3
				ON D.x = d3.x ) d2
		ON A.x = d2.x
GO

DROP TABLE A
DROP TABLE B
DROP TABLE C
DROP TABLE D
DROP TABLE E
DROP TABLE F

rockmoose

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-04-01 : 16:50:46

Tara,

I've been reading this with rapt fascination (I learn so much here!). And although this may be of little help, I agree with Dr. Cross Join. Breaking it up into smaller parts seems to not only alleviate the complexity, but may be easier to grasp. It's like counting cards, I found that simply keeping track of the face cards gave me roughly the same results as trying to keep track of the lesser valued cards.

I hope this helps.

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-04-01 : 16:54:25

The derived table solution is what I have in production already as at the time I thought it was most readable (not sure why I changed my mind this week), but now I'm going to use a view for the repeated code that I have as Jeff suggested. I had considered doing this in the past, but at the time didn't realize how often I'd need to use the same snippet of code over and over again. It's now duplicated over 10 stored procedures, so I'll need to go back and fix those up.

Tara

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-04-01 : 17:11:17

What Xerxes said,
The main problem with the nested join is that You create one big monster query.
And not many people will be able to read it anyway!

Off topic - I did read Your comment about NULLS Xerxes, thanks!

rockmoose

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-04-01 : 17:27:37

Thanks for the kudos, Moose!

But I have to give credit where credit is due, I learned about simplifying my queries from you all....and it works!

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-04-01 : 17:41:13

quote:
Originally posted by Xerxes

Thanks for the kudos, Moose! But I have to give credit where credit is due, I learned about simplifying my queries from you all....and it works!

Yes, you are the man with the CURSORS right

?
And the father of seven!
Respect.

rockmoose

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-04-01 : 18:36:04

You are too cool 'Moose!

I'm now a memeber of Cursor Users Anonymous (CUA). My 12-step program to avoid cursors is:

1.) Don't use cursors
2.) There's probably another way to do this
3.) There HAS to be another way to do this
4.) Looping is NOT the answer. And it's probably slower!
5.) Loops take you back where you've already been!
6.) The only good loops are Froot Loops

7.) Cursors are the darkside of SQL.
8.) Don't use cursors. (Did I already say that?)
9.) Please don't use cursors.
10.) Ohmigod! Stop, before it's too late!
11.) Put those cursors away before they put you away!
12.) Confess all to SQLTeam.com. Receive 3 whacks. ("Sir, may I have another!")

How's that?