Outer self join filters vs subqueries 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?
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' ;