0

My query looks like:

   SELECT * 
     FROM users U 
LEFT JOIN posts P ON P.userId = U.id AND P.userId IS NOT NULL;

Why the query also return result where userId is null ?

I know that for my needs I can use INNER JOIN to get only posts related to user but is so strange that LEFT JOIN support multiple conditions, but not work with NOT NULL conditions.

user487772
  • 8,800
  • 5
  • 47
  • 72
Wizard
  • 10,985
  • 38
  • 91
  • 165
  • 2
    Put this on `WHERE` condition. – Sagar Gangwal Jun 13 '17 at 11:19
  • Learn what LEFT JOIN ON returns: INNER JOIN ON rows UNION ALL unmatched left table rows extended by NULLs. Always know what INNER JOIN you want as part of an OUTER JOIN. – philipxy Jun 21 '19 at 07:16
  • Does this answer your question? [What is the difference between “INNER JOIN” and “OUTER JOIN”?](https://stackoverflow.com/a/46091641/3404097) – philipxy Jun 27 '22 at 23:29
  • Does this answer your question? [What is the difference between "INNER JOIN" and "OUTER JOIN"?](https://stackoverflow.com/questions/38549/what-is-the-difference-between-inner-join-and-outer-join) – philipxy Aug 11 '23 at 11:50

5 Answers5

9

This is because "posts" does not contain the null-values and hence they can´t be filtered at that stage. The Null-values are only generated trough the join, when the server can´t find a corresponding row on the right table. So just put the not null in the where clause and it will work:

SELECT * FROM users U LEFT JOIN posts P ON P.userId = U.id WHERE userId IS NOT NULL;

(EDIT: You should use an inner join for productive work though, as it is the proper way and will give you much greater performance.)

You can also see all users who don´t have posts by inverting that:

SELECT * FROM users U LEFT JOIN posts P ON P.userId = U.id WHERE userId IS  NULL;
Daniel
  • 426
  • 3
  • 14
  • 2
    The first query is nothing more than an obfuscated inner join of course. – Thorsten Kettner Jun 13 '17 at 11:27
  • 1
    Shure, but as stated in the question, the Author already knows that inner join would be the proper way. Maybe it helps to unsderstand the mechanics of join though, in that it creates a new "virtual" table. – Daniel Jun 13 '17 at 11:34
0

You are outer joining the posts table. This means for every users record that has no match in posts you still get this record with all posts columns null.

So say you have a users record with userid = 5 and there is no posts record with id = 5.

ON P.userId = U.id AND P.userId IS NOT NULL

The two combined conditions are not met (there is no record with userid 5), so you get the users record with all posts columns set to null in your results.

Maybe you are simply looking for an inner join? All users records with their posts data?

Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
0

This query:

SELECT *
FROM users U LEFT JOIN
     posts P
     ON P.userId = U.id AND P.userId IS NOT NULL;

Returns all rows in the users as well as all columns from posts, regardless of whether or not they match. This is true, regardless of whether the ON clause evaluates to TRUE or FALSE.

What you want is a WHERE. In addition, you should only select the columns from users:

SELECT u.*
FROM users U LEFT JOIN
     posts P
     ON P.userId = U.id 
WHERE P.userId IS NOT NULL;

Note that you can also accomplish this using NOT IN or NOT EXISTS.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

Because the LEFT JOIN must return every row from the left table by it's definition. The raw may be augmented with the data of the right table depending on the ON clause evaluation. So the following code must return a row.

select u.*, p.*
from ( 
    select 1 as id 
    ) u
left join (
    -- no data at all
    select 2 as id where 1=2
    ) p on 3 = 4 -- never is true
Serg
  • 22,285
  • 5
  • 21
  • 48
-1

Try this 

SELECT * FROM users U LEFT JOIN posts P ON P.userId = U.id
SELECT * FROM users U LEFT JOIN posts P ON P.userId = U.id where P.userId IS NOT NULL;
Paul Roub
  • 36,322
  • 27
  • 84
  • 93
kuldeep upadhyay
  • 42
  • 1
  • 8