Does MySQL ignore AND within the LEFT JOIN clause?

Question

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

This is the nature of `LEFT` join. Do you understand the difference between `INNER` and `OUTER` joins? — PM 77-1, Jun 20 '17 at 14:54
Possible duplicate of [SQL join: where clause vs. on clause](https://stackoverflow.com/questions/354070/sql-join-where-clause-vs-on-clause) — Utsav, Jun 20 '17 at 15:08
So where I think your'e getting hung up is on the `order.active = 1`. And the question is "Why is a join condition limit not applying to the results; when if you applied a filter to the user such as user.name like 'K%' you'd only get back users that start with K and their related orders. So why does it work one way but not the other? — xQbert, Jun 20 '17 at 15:18
I got hung up on using LEFT JOIN when trying to filter records of the first table, somehow thinking the AND condition will remove records of first table for me. — Dennis, Jun 20 '17 at 15:23
You cannot filter FIRST table in join clause. Only SECOND table ) — Максим Степанов, Mar 15 '20 at 23:41

Evaldas Buinauskas · Accepted Answer · 2017-06-20T15:11:54.730

2

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.

edited Jun 20 '17 at 15:11

answered Jun 20 '17 at 14:55

Evaldas Buinauskas

13,739
11
55
107

You've covered all the same points as me but worded it better - removed my answer. – Bridge Jun 20 '17 at 14:59
...except the last part - it's not an inner join, rows from Order not matching a user will still be returned – Bridge Jun 20 '17 at 15:01
@Bridge I guess it can be said that I won race condition ☺️ – Evaldas Buinauskas Jun 20 '17 at 15:01
I understand your 2nd paragraph but not the first. I am using both conditions, true, one of which is `active` must be `1`, but it also returns a `0`, so there is a dissonance. I'm still trying to work through that. – Dennis Jun 20 '17 at 15:02
You're right, missed that. But generally speaking if you filter values from right table in where clause, it becomes inner join (at least logically) – Evaldas Buinauskas Jun 20 '17 at 15:02
That's not true, user can be null because you're left joining that table. Let me update my answer and explicitly tell what your queries return – Evaldas Buinauskas Jun 20 '17 at 15:04
correct, my mistake, `user` can be null... but when `user` is not null ... eh not yet seeing that case – Dennis Jun 20 '17 at 15:05
See updates answer. I have explained what is the difference between these two queries. – Evaldas Buinauskas Jun 20 '17 at 15:07
ok thanks I think I got it.. Basically my first query LEFT JOINs user ONLY when `active = 1`, as it is supposed to do, otherwise it JOINs NULL, even when user is available to be JOINed (were it not for the `active = 1` clause). In other words, AND within the LEFT JOIN does not help in filtering out records of the first table – Dennis Jun 20 '17 at 15:18
Yep, any condition will be applied just to your user table. It can be even `1=1` – Evaldas Buinauskas Jun 20 '17 at 15:21

Does MySQL ignore AND within the LEFT JOIN clause?

1 Answers1