Left join doesn't return all results from first table

Question

I have a table that is used for reference (number), as follows

----------------------------
Id | Name | Description    |
----------------------------
1    One    The number one
2    Two    The number two
3    Three  The number three

And then another table (user_number) that references these values but for specific users

--------------------------
Id | User_Id | Number_Id |
--------------------------
1    400      1
2    400      2

I want to retrieve all the results of the first table but I want to see where they match up also. I have tried to use the following query but it only returns what an INNER JOIN would return

SELECT n.Id, n.Name, n.Description, un.Id As Active_Number
FROM number n
LEFT JOIN user_number un
ON n.Id = un.Id
WHERE un.User_Id = 400;

This query returns:

[
    {
        "Id": 1,
        "Name": "One",
        "Description": "The number one",
        "Active_Number": 1
    },
    {
        "Id": 2,
        "Name": "Two",
        "Description": "The number two",
        "Active_Number": 2
    }
]

However this doesn't return the third set of values from the number table.

Answer 1

Dont' know what DB you're using but try

SELECT n.Id, n.Name, n.Description, un.Id As Active_Number
FROM number n
LEFT JOIN user_number un
ON n.Id = un.Id and un.User_Id = 400;

Answer 2

Your WHERE clause is making this into an INNER JOIN. Put that in the ON clause instead:

SELECT n.Id, n.Name, n.Description, un.Id As Active_Number
FROM number n
LEFT JOIN user_number un
ON n.Id = un.Id
AND un.User_Id = 400;

Answer 3

Because in your where condition, you are eliminating all records where User ID is not 400 - which excludes any row in the left table, but not the right.

Based on the where condition placement, you're applying that filter to the whole query, AFTER the join. What you should od instead is this:

SELECT n.Id, n.Name, n.Description, un.Id As Active_Number
FROM number n
LEFT JOIN (select * from user_number 
WHERE un.User_Id = 400 ) un
ON n.Id = un.Id;

EDIT: the other responder's answer is the better choice, which eluded me when I was typing up this code - which works also, but is not the ideal solution.

Answer 4

Your LEFT JOIN does return all results from the first table. Here's the result of the LEFT JOIN:

-------------------------------------------------------
Id | Name | Description     | Id | User_Id | Number_Id |
-------------------------------------------------------
1    One    The number one    1    400       1
2    Two    The number two    2    400       2
3    Three  The number three  NULL NULL      NULL

Here, if you filter on User_Id = 400, it should be obvious why the third row is excluded.

A common suggestion when seeing these results is changing the filter to WHERE User_Id IS NULL OR User_Id = 400. Don't do this.

Suppose you have other records in your second table with a different user ID, so that the left join result looks like:

-------------------------------------------------------
Id | Name | Description     | Id | User_Id | Number_Id |
-------------------------------------------------------
1    One    The number one    1    400       1
2    Two    The number two    2    400       2
2    Two    The number two    3    500       2
3    Three  The number three  4    500       3

Here, it should be obvious that again, you'd be removing the last row.

The other answers you've received about moving the WHERE condition to the join condition will work, but a more logical, IMO, approach is to use a subquery:

SELECT n.Id, n.Name, n.Description, un.Id As Active_Number
FROM number n
LEFT JOIN (
    SELECT *
    FROM user_number
    WHERE user_id = 400
) AS un
ON n.Id = un.Id;

This reduces the data set to which you're joining, to only the data to which you actually want to join. But the result is the same as putting the user_id = 400 check in the join condition.

Answer 5

Always put the join conditions in the on clause If you are doing an inner join, so do not add any where conditions to the on clause, put them in the where clause

If you are doing a left join, add any where conditions to the on clause for the table in the right side of the join. This is a must because adding a where clause that references the right side of the join will convert the join to an inner join (With one exception described below).

SELECT n.Id, n.Name, n.Description, un.Id As Active_Number
FROM number n
LEFT JOIN user_number un
ON n.Id = un.Id
AND un.User_Id = 400;

Answer 6

Remove the WHERE condition and it will work as you intended. What you are doing basically is getting the left join correctly and then filtering out the 3rd row.

If you want to get all 3 results you can do:

SELECT n.Id, n.Name, n.Description, un.Id As Active_Number
FROM number n
LEFT JOIN user_number un
ON n.Id = un.Id
WHERE u.Id < 4;

In other words, the table you should be filtering is the left one on the left join (number).