Question 294199 in Mysql Question posted in
The official MySQL documentation can be found here.

`is not true` does not work currectly in mysql

I have two tables flaw and userdata. There are uid column in flaw table and FlawId, IsDeleted(can be null) columns in userdata table.
I want to get all flaw not deleted by user using following sql:

select *
from flaw f
left join userdata `user` on f.uid = `user`.FlawId 
where `user`.IsDeleted is not true;

But the result only contains flaw rows that has corresponding rows in userdata and the IsDeleted value is not true.

For example: If flaw table contains:

uid 
1
2
3

and userdata contains :

FlawId | IsDeleted
1      | null

Then the sql above will only return:

uid | FlawId | IsDeleted
1   | 1      | null

But what I expect is:

uid | FlawId | IsDeleted
1   | 1      | null
2   | null   | null
3   | null   | null

Edit1:
I have tried move condition in WHERE clause to ON clause, then it work as expected. I want to know the reason of this wired behavior. I’m using mysql 5.7.36.

Edit2:
As SelVazi said in comment, this is a bug

Tags:

2

Answers


  1. 0

    Left join behaves like an inner join and returns all rows from the left side that have matching rows on the right side.

    Consider using outer join to return all rows instead: https://www.geeksforgeeks.org/sql-outer-join/

    Login or Signup to reply.
  2. 0

    This seems to be an issue with the

    where `user`.IsDeleted is not true;

    condition (at least in MySql 5.7, seems to work in MySql 8). You can work around it like this

    select f.flawid, u.isdeleted
from flaws f left join users u on f.flawid = u.flawid
where u.isdeleted is null or u.isdeleted = false

    See also this fiddle https://www.db-fiddle.com/f/jdZkgqjtuxVEj9hp9sQjsX/3

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search