minuses of using 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

General SQL Server Forums

New to SQL Server Programming

minuses of using Outer join?

Author

Topic

y0zh
Yak Posting Veteran

60 Posts

Posted - 2010-04-22 : 10:17:00

Could somebody explain me minuses of using Outer join?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-22 : 10:20:37

what do you mean by minuses?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

y0zh
Yak Posting Veteran

60 Posts

Posted - 2010-04-22 : 10:27:09

I mean could we expact some disadvantages while using outer join

y0zh
Yak Posting Veteran

60 Posts

Posted - 2010-04-23 : 12:45:55

Any answer?

I suppose that it could be NULL values.. am I right?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-23 : 12:47:13

quote:
Originally posted by y0zh

Any answer?

I suppose that it could be NULL values.. am I right?

how can NULL values be a minus. there are cases where you want values from one table even if it doesnt have match in others. In such cases only we use OUTER JOINS and get NULL values

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2010-04-23 : 12:56:36

every join serves its purpose, when used correctly there's only advantages, when not used correctly there's only disadvantages.

quote:
Originally posted by y0zh

I mean could we expact some disadvantages while using outer join

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-04-24 : 03:37:39

1. "potentially" more data rows returned to the result set compared to an inner join....and thus potentially a slower overall action.

2. "potentially" columns with null values in the result which may break your application data validation rules

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-04-24 : 03:43:16

try to avoid right outer joins and set your data to use at least left outer join. but if possible, set and prepare your data in query in a way that you can use only left or inner join.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-04-24 : 07:41:31

quote:
Originally posted by slimt_slimt

try to avoid right outer joins and set your data to use at least left outer join. but if possible, set and prepare your data in query in a way that you can use only left or inner join.

Why?

--
Gail Shaw
SQL Server MVP

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-04-24 : 09:11:30

right outer joins are very cost expensive and are mainly results of poor query design.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-04-24 : 09:53:01

Have you got tests that show that the right outer join is very expensive in comparison with the left?


CREATE TABLE PrimaryTable_Small ( 
ID INT IDENTITY PRIMARY KEY, 
SomeColumn CHAR(4) NULL, 
Filler CHAR(100) 
);

CREATE TABLE SecondaryTable_Small ( 
ID INT IDENTITY PRIMARY KEY, 
LookupColumn CHAR(4) NULL, 
SomeArbDate DATETIME DEFAULT GETDATE() 
);

INSERT INTO PrimaryTable_Small (SomeColumn) 
SELECT top 2500 
CHAR(65+FLOOR(RAND(a.column_id *5645 + b.object_id)*10)) + CHAR(65+FLOOR(RAND(b.column_id *3784 + b.object_id)*12)) + 
CHAR(65+FLOOR(RAND(b.column_id *6841 + a.object_id)*12)) + CHAR(65+FLOOR(RAND(a.column_id *7544 + b.object_id)*8)) 
from msdb.sys.columns a cross join msdb.sys.columns b 

INSERT INTO SecondaryTable_Small (LookupColumn) 
SELECT DISTINCT SomeColumn 
FROM PrimaryTable_Small TABLESAMPLE (25 PERCENT) -- 239 rows
GO

CREATE NONCLUSTERED INDEX idx_PrimaryTableSmall_SomeColumn ON PrimaryTable_Small (SomeColumn)
CREATE NONCLUSTERED INDEX idx_SecondaryTableSmall_LookupColumn ON SecondaryTable_Small (LookupColumn)
GO

SELECT PrimaryTable_Small.ID, PrimaryTable_Small.SomeColumn
  FROM PrimaryTable_Small
    LEFT OUTER JOIN SecondaryTable_Small ON PrimaryTable_Small.SomeColumn = SecondaryTable_Small.LookupColumn

SELECT PrimaryTable_Small.ID, PrimaryTable_Small.SomeColumn
  FROM SecondaryTable_Small
    RIGHT OUTER JOIN PrimaryTable_Small ON PrimaryTable_Small.SomeColumn = SecondaryTable_Small.LookupColumn

IO and Time statistics:

Left Outer Join:
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0.
Table 'PrimaryTable_Small'. Scan count 1, logical reads 7, physical reads 0.
Table 'SecondaryTable_Small'. Scan count 1, logical reads 2, physical reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 973 ms.

Right Outer Join:
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0.
Table 'PrimaryTable_Small'. Scan count 1, logical reads 7, physical reads 0.
Table 'SecondaryTable_Small'. Scan count 1, logical reads 2, physical reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 704 ms.

A right outer join is just a left outer join with the tables switched in position. The one you may be thinking about that usually indicates poor table design is the full outer join. While there may well be cases where right outer join is slower, it's not true as a general case.

--
Gail Shaw
SQL Server MVP

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-26 : 03:58:26

It is mostly the readablility and how you used to write it rather than performance issues
http://weblogs.sqlteam.com/jeffs/archive/2008/02/13/on-right-outer-joins.aspx

Madhivanan

Failing to plan is Planning to fail

dportas
Yak Posting Veteran

53 Posts

Posted - 2010-04-26 : 04:50:59

Right outer join is just a different syntax for exactly the same operation as a left outer join. I don't believe there is any difference in performance.

A disadvantage of outer joins in SQL is that they generate nulls in the result set. Those nulls are indistinguishable from other nulls that are not generated by the outer join operation. There is no "standard" semantics for nulls in SQL but in many common situations the appearance of nulls in outer joins doesn't really correspond to the way nulls are returned and used in other places. Therefore the presence of nulls in outer joins creates a certain amount of ambiguity.

It would be better if the "outer" join syntax provided an easy way for the user to specify the set of values returned instead of just defaulting to a row of nulls.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources