CODEWITHSUNDEEP
sqlpostgresql
smit
smit

Reputation: 282

Join tables when a value isn't there in the left table

I have got 2 tables with some data and trying to join them when the value isn't there in the clients table and also in the custom_clients table. It works fine when the joining the table when a value isn't in the custom_clients table, but doesn't when the value isn't there in the clients table.

clients
id language_id key            text
1   1        client_account   new account
2   2        client_signin    signin


custom_clients
id language_id key            text
1   1        client_name      name
2   2        client_signin    login

The output should be a table like
language_id key            text
1        client_account   new account
1        client_name      name
2        client_signin    login

This is what I have tried

SELECT language_id, key, text FROM clients 
INNER JOIN custom_clients ON custom_clients.language_id = clients.language_id

Upvotes: 0

Views: 160

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520898

One approach uses a full outer join:

SELECT
    COALESCE(cc.language_id, c.language_id) AS language_id,
    COALESCE(cc."key", c."key") AS "key",
    COALESCE(cc.text, c.text) AS text
FROM clients c
FULL OUTER JOIN custom_clients cc
    ON cc.language_id = c.language_id AND
       cc."key" = c."key";

Demo

EDIT:

This can be simplified a little bit with the USING clause:

SELECT language_id, "key",
       COALESCE(cc.text, c.text) AS text
FROM clients c FULL OUTER JOIN
     custom_clients cc
     USING (language_id, "key");

Upvotes: 3

Related Questions