Outer self join filters vs subqueries

Question

I have a table storing information about tickets. There is one record when ticket is sold and another when ticket is used. There is a column called TransType which is set to either 'sold' or 'used' to mark which one it is. There are other columns in the tables and some of them contains values when it is sales but not when it is used and vice versa. The table is actually a fact-table in a data warehouse style.

Amoung other things I am calculating time difference between sales and used so I am joining the table to itself in order to get one record for each ticket to be able to have timestamps for both event in the same record to calculate.

I need to include all sold tickets so an outer join should solve that.

First I ran this query

select x.* 
from factI as x
left join factI as y on x.tickedId = y.tickedId
where x.TransType = 'sold'
and y.TransType = 'used'

When I run it the filter x.TransType = 'sold' is not working and the query return results for all records in factI, regardless of TransType. If I use inner join this works but obviously not returning tickets which haven't been used.

so I changed the query, to this one which gives me correct results.

select * from (
   select * from factI where TransType = 'sold'
) as x
left join (
   select * from factI where TransType = 'used'
) as y on x.ticketId = y.ticketId

Why is it that the where clause in the first query isn't filtering out correctly when I use an outer (left) join?

Answer 1

Your first query works as an inner join because the y.TransType = 'used' condition which uses the right table is in the where clause.

Your second query can be rewritten without derived tables by simply moving that condition to the on clause:

select x.*, y.*
from factI as x
left join factI as y on  x.tickedId = y.tickedId
                     and y.TransType = 'used'
where x.TransType = 'sold' ;

Answer 2

In your first query, in the where clause, you're restricting results to those where y.TransType = 'used'. This turns the left join into an inner join because you're throwing away results where y.TransType is null (aka where the other ticket doesn't exist).

An easy fix for this is to move that condition to the on clause like this:

select x.* 
from factI as x
left join factI as y on x.tickedId = y.tickedId
    and y.TransType = 'used' -- moved the condition to the on clause
where x.TransType = 'sold'

As a general rule, I keep conditions for outer-joined columns in the on clause rather than the where clause. Otherwise you have to check for null:

select x.* 
from factI as x
left join factI as y on x.tickedId = y.tickedId
where x.TransType = 'sold'
and (y.TransType = 'used' or y.TransType is null)