MariaDB: LEFT OUTER JOIN does not return row

Question

I already tried various types of JOINS but I am not able to get this simple query to work. I would like to have the result of table a in any case, even if there is no corresponding entry in table b. I tried:

SELECT a.user_id,
       a.user_name,
       b.first_name
FROM   users a
LEFT OUTER JOIN members b
ON a.member_uid = b.uid
WHERE  (a.user_name = 'TEST'
    AND b.active = 1)

In this case, there is no entry in b that has b.active = 1. But I assumed that all wanted columns from a would be returned and the column from b would be null. But when running this query in the SQL window of the MariaDB, zero rows are returned.

Any help would be highly appreciated!!

Possible duplicate of [Left Outer Join doesn't return all rows from my left table?](https://stackoverflow.com/questions/4707673/left-outer-join-doesnt-return-all-rows-from-my-left-table) — philipxy, Sep 30 '19 at 21:35
his is a faq. Before considering posting please always google your error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags, & read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. — philipxy, Sep 30 '19 at 21:36
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. A WHERE or INNER JOIN ON that requires a right [sic] table column to be not NULL after an OUTER JOIN ON removes any rows extended by NULLs, ie leaves only INNER JOIN ON rows, ie "turns OUTER JOIN into INNER JOIN". You have that. — philipxy, Sep 30 '19 at 21:36
Please in code questions give a [mre]--cut & paste & runnable code; example input (as initialization code) with desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. That includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS/product & DDL, which includes constraints & indexes & tabular-formatted base table initialization. — philipxy, Sep 30 '19 at 21:37

score 1 · Accepted Answer · answered May 01 '17 at 09:12

Left Outer Join will get all the rows/data from table a whether they are matching or not-matching in table b. But you are again filtering out the data by putting conditions in where clause. Since, there is no entry in b that has b.active = 1 so there will be no output. Remove b.active = 1 from the query, like this :

SELECT a.user_id,
   a.user_name,
   b.first_name
FROM   users a
LEFT OUTER JOIN members b
ON a.member_uid = b.uid
WHERE a.user_name = 'TEST';

OK, agreed. So I need to check the active state after query execution. — AntonSack, May 01 '17 at 09:22

score 1 · Answer 2 · answered May 01 '17 at 17:08

It matters whether you put things in ON or WHERE when doing LEFT JOIN. ("OUTER" is ignored.)

SELECT a.user_id,
       a.user_name,
       b.first_name
FROM   users a
LEFT OUTER JOIN members b
    ON a.member_uid = b.uid
   AND b.active = 1              -- Note
WHERE  a.user_name = 'TEST'

Think of it this way:

If ON is false, keep the row, but with all NULLs.
If WHERE is false, don't return the row.

score 0 · Answer 3 · answered Sep 30 '19 at 18:46

I found a way to make it work with mariaDB, you make subqueries from both tables and then join. Not the best but it works:

SELECT a.user_id,
       a.user_name,
       b.first_name
FROM   (select * from users WHERE user_name = 'TEST') a
LEFT JOIN (SELECT * from members WHERE active = 1) b
ON a.member_uid = b.uid

If anyone knows the proper way to do this, please comment.

MariaDB: LEFT OUTER JOIN does not return row

3 Answers3

Linked