Dennis Dennis - 9 months ago 29
SQL Question

Does MySQL ignore AND within the LEFT JOIN clause?

When I try this:

FROM order
LEFT JOIN user on = order.user_id AND = 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:

FROM order
LEFT JOIN user on = order.user_id
WHERE = 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.


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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download