Explanation of a SQL curio with left/self joins

[rackman99]

Hi, While messing about trying to work out a way so that when a field does not have any data for a parameter all records are returned I stumbled along:
declare @schcode varchar(2)
SET @schcode = '13'
--returns records for 13 (13 is a valid school)
select * FROM udw.division as d
left join (select school_code from udw.school where school_code = @schcode)
as s on s.school_code = s.school_code
where d.school_code = isnull(s.school_code,d.school_Code)

--returns records for all schools (X is NOT a valid school)
SET @schcode = 'X'
select * FROM udw.division as d
left join (select school_code from udw.school where school_code = @schcode)
as s on s.school_code = s.school_code
where d.school_code = isnull(s.school_code,d.school_Code)

Now what I am trying to understand is why this works. Why would self joining the nested query and then using isnull allow code to return matching records where valid and non matching valids when NULL?

Just curious if anyone can explain why.

Cheers

 

[SQLSister]

If your second derived table has no results, then left joining to it where school_code is null would filter out no records becasue all the records would show school_code as null.

"NOTHING is more important in a database than integrity." ESquared

 

[Qik3Coder]

As SQLSister has stated it's because the data is null.

If you inspect your data, and the query, further you can understand why, and help to use this to your advantage.

FYI, you can use {CODE} stuff {/CODE} with hard brackets instead to wrap your code.

You have:

--returns records for all schools (X is NOT a valid school)
SET @schcode = 'X'
select * FROM udw.division as d
left join (select school_code from udw.school where school_code = @schcode) 
as s on s.school_code = s.school_code
where d.school_code = isnull(s.school_code,d.school_Code)

Now he did this as a sub-query join, but using his same basic "some or all" logic you can turn this into a straight select,because you don't need any data from the second table.

--returns records for all schools (X is NOT a valid school)
SET @schcode = 'X'
select d.* FROM udw.division as d
WHERE 
   d.school_code = @schoolCode 
   OR @schoolCode = 'x'

If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this [red]"there was an error crap"[/red]