Dennis Dennis - 5 months ago 23
SQL Question

Does MySQL ignore AND within the LEFT JOIN clause?

When I try this:

SELECT
order.id,
order.active,
user.id
FROM order
LEFT JOIN user on user.id = order.user_id AND order.active = 1;


it returns result set that has
active = 0 OR active = 1


Only after moving my AND clause into WHERE I get the intended result:

SELECT
order.id,
order.active,
user.id
FROM order
LEFT JOIN user on user.id = order.user_id
WHERE order.active = 1;


Now only rows with
active = 1
show up

Answer Source

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 user table.

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.

Update.

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.