4

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?

10

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' ;
1

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)
  • 2
    The second query is not 100% equivalent to the first, in 2 cases. 1, if TransType is nullable and 2 if TransType can have other values besides 'used' and 'sold'. – ypercubeᵀᴹ Apr 27 '16 at 13:33
  • @ypercubeᵀᴹ, If I change the second condition to (y.TransType = 'used' or y.ticketId is null), it'll fix the first problem you mentioned. Will it fix the second problem you mentioned? I can't figure out how having a third TransType state can cause a problem with the query. – user2023861 Apr 28 '16 at 12:57
  • Yes on the first. On the second problem, if there is a ticket that is 'sold' and then (say) 'returned' (but not a row with 'used') then this ticket will be shown in the results of the first query (as "sold" but not "used") but it will NOT be shown in the results of the second query. So, it depends on what the OP wants the results to be. – ypercubeᵀᴹ Apr 28 '16 at 13:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.