When I try this:
LEFT JOIN user on user.id = order.user_id AND order.active = 1;
active = 0 OR active = 1
LEFT JOIN user on user.id = order.user_id
WHERE order.active = 1;
active = 1
That's how it's supposed to work. You're left joining on
user using two conditions and both of them have to be met in order to retrieve records from it, otherwise you'll see null values coming from
Now in second query, you're joining just by a single condition AND THEN additionally filter joined result set, which then simply becomes a inner join in your case.
Your first query will return every single order from your table and will optionally return user if that order is active. Non active orders will have null as user. It doesn't limit your orders, it just tells MySQL to return user for each active record
Second query will bring back all active orders and optionally user for that order.