SQL join return results if where clause does not match

Question

I’m trying to return values from a join where the where clause might not have a matches.

Here’s my database schema

strings
-------
id: INT
name: VARCHAR
value: VARCHAR
fileId: INT FOREIGN KEY files(id)

languages
---------
id: INT
code: CHAR
name: VARCHAR

translations
------------
id: INT
string_id: INT, FOREIGN KEY strings(id)
language_id: INT, FOREIGN KEY languages(id)
translation: VARCHAR

I’m trying to select all the strings, and all the translations in a given language. The translations may or may not exist for a given language, but I want to return the strings any way.

I’m using a query similar to:

SELECT s.id, s.name, s.value, t.translation
FROM strings s LEFT OUTER JOIN translations t ON s.id = t.string_id
WHERE s.file_id = $1 AND t.language_id = $2

I want to return strings regardless of whether matches are found in the translations table. If translations don’t exist for a given language, that field would of course be null. I think the problem is with the WHERE clause having the t.language_id = ..., since language_id doesn't exist in this particular case. But not sure the best way to fix this.

Database Postgresql

Does this answer your question? [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, Feb 15 '20 at 18:26
This is a faq. Before considering posting please always google any 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, Feb 15 '20 at 18:27
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. For errors 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 include DDL & tabular initialization. When you get a result that you don't expect, find out what your misunderstanding is.--Isolate the first unexpected subexpression & its input & output. (Debugging fundamental.) — philipxy, Feb 15 '20 at 18:30
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. After an OUTER JOIN ON, a WHERE or INNER JOIN that requires a right [sic] table column to be not NULL removes any rows with extending NULLs, ie leaves only INNER JOIN ON rows, ie "turns OUTER JOIN into INNER JOIN". You have that — philipxy, Feb 15 '20 at 18:31

score 3 · Accepted Answer · answered Feb 15 '20 at 16:44

Conditions on the second table need to go in the ON clause:

SELECT s.id, s.name, s.value, t.translation
FROM strings s LEFT OUTER JOIN
     translations t
     ON s.id = t.string_id AND t.language_id = $2
WHERE s.file_id = $1;

Otherwise, t.language_id is NULL and that fails the comparison in the WHERE clause.

SQL join return results if where clause does not match

1 Answers1