MYSQL ALL rows from first table and join columns from other table with where clause

Question

I have 2 tables and I want all the rows of the first table, the union with the second table will depend on 2 things, the first, that the ID_NAMES match and the second, that the column OPENED is zero , otherwise fill with null. Example

First table names

ID_NAMES   NAME
   1       test1
   2       test2
   3       test3
   4       test4

Second Table surprise

ID_SURPRICE  ID_NAMES   ELEMENT   OPENED
     1           3        lion        0
     2           2         dog        1
     3           1         cat        0

I want the next result

ID_NAMES NAMES ID_SURPRICE  ID_NAMES   ELEMENT   OPENED
     1   test1      3          1         cat        0
     2   test2     null       null       null      null
     3   test3      1          3         lion       0
     4   test4     null       null       null      null

I perform the query in the following way but it only returns the ones that coincide with the where

SELECT names.*, surprise.* 
FROM names
    LEFT JOIN surprise
        ON names.ID_NAMES = surprise.ID_NAMES 
where surprise.OPENED = 0;

score 1 · Accepted Answer · answered Aug 21 '21 at 01:45

I want the answer here:

LEFT JOIN query not returning all rows in first table

Basically, if I add a where I am doing an inner join, so I have to move the clause to the on

SELECT names.*, surprise.* 
FROM names
    LEFT JOIN surprise
        ON names.ID_NAMES = surprise.ID_NAMES AND surprise.OPENED = 0;

score 0 · Answer 2 · answered Aug 21 '21 at 05:01

Although you used left join but the overall query is acting like inner join that's why you didn't get your expected result.

See the changes

SELECT names.*, surprise.* 
FROM names
    LEFT JOIN surprise
        ON names.ID_NAMES = surprise.ID_NAMES and surprise.OPENED = 0
;

Tips: When you use left, right join then you need to be careful at this scenario. Using condition in where clause the query will perform like inner join

MYSQL ALL rows from first table and join columns from other table with where clause

2 Answers2