OUTER JOIN turns into LEFT JOIN??? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 turns into LEFT JOIN???

Author

Topic

zelk
Starting Member

6 Posts

Posted - 2005-08-09 : 08:10:19

Hello!

I have a query that retrieves data from 3 tables. I have made a simplified version of my problem to post here.

When I run the query below without the WHERE-statement, everything works as I expect, in that all rows from tbl_1 and all rows from tbl_2 are matched together using an OUTER JOIN.


SELECT
  tbl_1.a,
  tbl_1.b,
  tbl_2.a,
  tbl_2.b
FROM
  tbl_1
RIGHT OUTER JOIN
  tbl_3
  ON
  (
    tbl_1.c = tbl_3.c
  )
FULL OUTER JOIN
  tbl_2
  ON
  (
    tbl_1.a = tbl_2.a
    AND
    tbl_2.c = tbl_3.c
    AND
    tbl_1.c = tbl_3.c
  )
--WHERE
--  tbl_3.c = 'rickytest'
ORDER BY
  tbl_2.a,
  tbl_1.a

This is the result when running the query WITHOUT the WHERE-statement. All fine (since I have only one record in tbl_3 things look perfect even though the WHERE-statement will be needed in the true scenario).

a,b,a,b
2005-01-01 00:00:00,500.0000,,
2005-02-01 00:00:00,501.0000,2005-02-01 00:00:00,600.0000
2005-03-01 00:00:00,502.0000,2005-03-01 00:00:00,601.0000
,,2005-04-01 00:00:00,602.0000
,,2005-05-01 00:00:00,603.0000

One can clearly see that the OUTER JOIN works here since one row has NULL values in the tbl_2 data and two rows have NULL values in the tbl_1 data.

As soon as I run the query including the WHERE-statement, SQL Server decides to make a LEFT JOIN instead of an OUTER JOIN. WHY!?!?!? And most of all, how can I solve this?

Below is the result as soon as I include the WHERE-statement in my query. By the way, I have carefully looked at the execution plans to see that the OUTER JOIN really turns into a LEFT JOIN.

a,b,a,b
2005-01-01 00:00:00,500.0000,,
2005-02-01 00:00:00,501.0000,2005-02-01 00:00:00,600.0000
2005-03-01 00:00:00,502.0000,2005-03-01 00:00:00,601.0000

I can post the table definitions and it's data if needed.

/Ricky

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-08-09 : 08:21:09

Hi Ricky,

Remove the WHERE condition,
and make it a JOIN condition instead.

SELECT
  tbl_1.a,
  tbl_1.b,
  tbl_2.a,
  tbl_2.b
FROM
  tbl_1
RIGHT OUTER JOIN
  tbl_3
  ON
  (
    tbl_1.c = tbl_3.c
    AND tbl_3.c = 'rickytest'
  )
FULL OUTER JOIN
  tbl_2
  ON
  (
    tbl_1.a = tbl_2.a
    AND
    tbl_2.c = tbl_3.c
    AND
    tbl_1.c = tbl_3.c
  )
--WHERE
--  tbl_3.c = 'rickytest'
ORDER BY
  tbl_2.a,
  tbl_1.a

Any help ?

rockmoose

zelk
Starting Member

6 Posts

Posted - 2005-08-09 : 08:36:02

Ah... of course. Thank you!!! I was going blind looking at that SQL statement. :)

/Ricky

(ricky edit: Nope, I was wrong. Read on...)

zelk
Starting Member

6 Posts

Posted - 2005-08-09 : 09:26:45

I realized that this did not work for my purposes either, since the comparison against 'rickytest' will be used in the joins... and since an outer join returns all records, even when they do not match the ON-statement, I will have too many records back, when adding more records to tbl_3... so, finally, the only solution I could find was using UNION, like this:


SELECT
	tbl_1.a,
	tbl_1.b,
	tbl_2.a,
	tbl_2.b
FROM
	tbl_1
INNER JOIN
	tbl_3
	ON
	(
		tbl_1.c = tbl_3.c
	)
LEFT OUTER JOIN
	tbl_2
	ON
	(
		tbl_1.a = tbl_2.a
		AND
		tbl_2.c = tbl_3.c
		AND
		tbl_1.c = tbl_3.c
	)
WHERE
	tbl_3.c = 'rickytest'


UNION

SELECT
	tbl_1.a,
	tbl_1.b,
	tbl_2.a,
	tbl_2.b
FROM
	tbl_2
INNER JOIN
	tbl_3
	ON
	(
		tbl_2.c = tbl_3.c
	)
LEFT OUTER JOIN
	tbl_1
	ON
	(
		tbl_1.a = tbl_2.a
		AND
		tbl_1.c = tbl_3.c
		AND
		tbl_2.c = tbl_3.c
	)
WHERE
	tbl_3.c = 'rickytest'
ORDER BY
	tbl_2.a,
	tbl_1.a

The result is now as in the first example, even when adding more records to tbl_3.

/Ricky

zelk
Starting Member

6 Posts

Posted - 2005-08-09 : 09:28:56

I am still interested to see if there are other solutions to this... since this approach is a bit clumsy.

/Ricky

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-08-09 : 09:38:33

here is a good example:


Declare @myTable1 table (id int, a int)
Insert Into @myTable1 Select 1, 100 Union All Select 3, 300 Union All Select 5, 500

Declare @myTable2 table (id int, b int)
Insert Into @myTable2 Select 1, 100 Union All Select 2, 200 Union All Select 4, 400

Declare @myTable3 table (id int, c int)
Insert Into @myTable3 Select 1, 1 Union All Select 2, 1 Union All Select 3, 1 Union All 
Select 4, 0 Union All Select 5, 0


Select * 
From @myTable3 as t3
Full Join @myTable1 as t1
On t3.id = t1.id
Full Join @myTable2 as t2
On t3.id = t2.id

Select * 
From @myTable3 as t3
Full Join @myTable1 as t1
On t3.id = t1.id
Full Join @myTable2 as t2
On t3.id = t2.id
Where t3.c = 1

Select * 
From @myTable3 as t3
Full Join @myTable1 as t1
On t3.id = t1.id
Full Join @myTable2 as t2
On t3.id = t2.id
Where t3.c = 0

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-08-09 : 10:12:01

quote:
Originally posted by zelk

I am still interested to see if there are other solutions to this... since this approach is a bit clumsy.

/Ricky

Post some sample data that we can use if You want.

Edit: spelling

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources