An SQL question

[Former Member]

I came across a colleague's query that had sub-selects to get the correct result set. Thinking it could be done with a join instead I tried but couldn't get the right results. I tracked it down to the use of a predicate but don't understand why it works the way it does.

With the data below,

select a.ValCol as "A",b.ValCol as "B",c.ValCol as "C"

from TableA a join TableB b on a.KeyCol = b.KeyCol

                     left outer join TableC c on c.KeyCol = b.KeyCol

where c.Usable = 'Y';

gives 3 rows instead of the expected 7.....

select a.ValCol as "A",b.ValCol as "B",c.ValCol as "C"

from TableA a join TableB b on a.KeyCol = b.KeyCol

                     left outer join TableC c on c.KeyCol = b.KeyCol and c.Usable = 'Y';

... does give the 7 I expect. Can anyone please explain why it works with the condition in the join rather that a where?

TIA, Paul

create table TableA(KeyCol integer,ValCol char(2));

insert into TableA values(1,'AA');

insert into TableA values(2,'AB');

insert into TableA values(3,'AC');

insert into TableA values(4,'AD');

insert into TableA values(5,'AE');

insert into TableA values(6,'AF');

insert into TableA values(7,'AG');

insert into TableA values(8,'AH');

insert into TableA values(9,'AI');

insert into TableA values(10,'AJ');

create table TableB(KeyCol integer,ValCol char(2));

insert into TableB values(1,'BA');

insert into TableB values(2,'BB');

insert into TableB values(4,'BD');

insert into TableB values(6,'BF');

insert into TableB values(7,'BG');

insert into TableB values(8,'BH');

insert into TableB values(10,'BJ');

create table TableC(KeyCol integer,ValCol char(2),Usable char(1));

insert into TableC values(1,'CA','Y');

insert into TableC values(4,'CD','Y');

insert into TableC values(6,'CF','N');

insert into TableC values(8,'CH','Y');

[former_member188493 · Accepted solution]

Your WHERE clause has effectively turned the LEFT OUTER JOIN into an INNER JOIN.

See item # 1 on this list of "characteristic errors".

One of the reasons people like my book is the 10-page "Logical Execution of a SELECT" which explains that the WHERE clause is evaluated after the FROM clause (with all its ON clauses) so all the candidate rows with NULL in TableC.Usable are eliminated.

About your solution...

IMO the ON clause should be reserved for join predicates.

I recommend NOT putting predicates like "c.Usable = 'Y'" in the ON clause.

A alternative is to use a simple derived table to "divide and conquer" the problem:

select a.ValCol as "A",b.ValCol as "B",c.ValCol as "C"
from TableA a 
join TableB b 
  on a.KeyCol = b.KeyCol
left outer join ( SELECT * FROM TableC where Usable = 'Y' ) c
  on c.KeyCol = b.KeyCol

The query engine will take care of optimizing the derived table; e.g., do not worry about the "SELECT *" doing too much work.

[Former Member · Accepted solution]

Thanks Breck. #1 eh? Fell at the first fence! I also dislike predicates in the ON clause and the aforementioned colleague uses a lot of selects in the column list, another dislike. That's what prompted the question.

Thanks too Volker and Koichi.

I like it around here - it's an education

[former_member194571]

Hi Paul,

there's one quite obvious oddity in your statement. The inner table of your outer join is TableC (alias c), which, if there's no match according to the join condition, will be represented by NULL values. These obviously are not = 'Y'. The WHERE clause you use removes all rows from the result set that are produced by the OUTER JOIN (on top of those in the INNER JOIN). Or, in other words, with the WHERE clause you use, the result of outer and inner join are identical. By making the "Usable = 'Y'" condition part of the join condition, the NULL value will be introduced to the result AFTER the condition was evaluated. On the opposite, the WHERE clause is evaluated AFTER the NULL values were introduced (and, for that reason, eliminates them again immediately).

HTH

Volker

[former_member182948]

Hi Paul,

Perhaps The following description will give the answer to your question.

"Outer joins and join conditions"

http://dcx.sap.com/index.html#sqla170/en/html/818caccb6ce21014aa5ae64d1d1abbf5.html

[former_member182948]

I showed an inappropriate link.

The following link is explanation of version 16.

"Outer joins and join conditions"

http://dcx.sap.com/index.html#sa160/en/dbusage/outer-on-joinsasp.html

[former_member182948]

Hi Paul.

You can see the behavior of the queries with a plan viewer.

It will help your understanding.

"Viewing a graphical plan"

http://dcx.sap.com/index.html#sqla170/en/html/8198e06d6ce210148d98a8718fa04a1a.html

[Former Member]

Thanks Koichi,

I'd forgotten the plan viewer. However, all it does is add confusion because using 'where ' the plan indicates that a is joined to c. This contradicts the (simplistic) descriptions of joins...

DocCommentXchange

BTW SQL Anywhere Personal Server Version 16.0.0.2127