MySQL 5.7 Right join returns unexpected count

Question

Table tickets:

This is a sales table & contains a 'ticket_type_id'. There are 4.9k rows (sales), e.g. ticket_type_id 1 has 1,451 rows, ticket_type_id 4 has 1,061 rows etc.

There are 18 values for ticket_type_id--1 through 10, 12, 13, 14, 17, 18, 19, 30, 41.

(I.e. tickets for ticket_type_id e.g. 11 and 22 have not sold yet.)

Table ticket_types:

This is a master list of all ticket types & contains an 'id', enabling a join to the tickets table (referential integrity enforced).

There are 35 values--1 through 19, 22, 23, 24, 29, 30, 31, 34, 35, 36, 38, 39, 40, 41, 42, 43, 44.

When I run a UNION I get a value of 53--correct. When I run a UNION ALL, I get a value of 35--correct.

select distinct ticket_type_id from tickets t 
where t.user_id != 2 
and t.status = 'active'  -- 18 rows
    UNION
-- UNION ALL
select distinct id from ticket_types tt -- 35 rows
order by 1

When I run a RIGHT JOIN on the ticket_types table, I get 18 rows returned. I.e. it returns only the matched values in the (left) tickets table.

-- RIGHT JOIN - doesn't work?
select t.ticket_type_id, tt.id
from tickets t
RIGHT OUTER join ticket_types tt
on t.ticket_type_id  = tt.id 
where t.user_id <> 2 and t.status = 'active' 
group by 1, 2

However, my expectation was that it would return 35 rows, the full set from the right (ticket_types) table.

I have also reversed the syntax and applied an equivalent LEFT join, getting the same results (18 rows, rather than 35).

Answer 1

Your right join will include those rows with tt.id that does not match any t.ticket_type_id, with NULL values for all t columns. But then you are excluding those with your t.user_id = 2 and t.status = 'active' conditions, effectively changing your right join to an inner join.

If you wish to include only t rows with those conditions, but also include tt rows with no matching t row, you must move those conditions from where to the on clause:

select t.ticket_type_id, tt.id
from tickets t
RIGHT OUTER join ticket_types tt
on t.ticket_type_id  = tt.id 
and t.user_id = 2 and t.status = 'active' 
group by 1,2