Join multiple tables without duplicated results and without UNION in PostgreSQL

Question

Lets assume we have two tables A and B in PostgreSQL:

A

title	first_group_id	second_group_id
My Title	1	2

B

group_id	element_name
1	First
1	Second
1	Third
2	Fourth
2	Fifth

I want to get the following result:

A.title	B0.element_name	B1.element_name
My Title	First
My Title	Second
My Title	Third
My Title		Fourth
My Title		Fifth

If I query something like the following - I will get 6 rows instead of 5 cause all rows from B0 will be mixed with all rows from B1 and the number of result rows will grow in progression if B0 or B1 have more rows:

SELECT A.title, B0.element_name, B1.element_name
LEFT JOIN B B0 ON A.first_group_id = B0.group_id
LEFT JOIN B B1 ON A.second_group_id = B1.group_id
FROM A;

A.title	B0.element_name	B1.element_name
My Title	First	Fourth
My Title	First	Fifth
My Title	Second	Fourth
My Title	Second	Fifth
My Title	Third	Fourth
My Title	Third	Fifth

I can achieve the desired behavior by using UNION but I wonder if there is a more efficient way to do that? Especially when it's required to make more than two JOINs in the same manner.

Answer 1

There may be a faster way. Works for your minimal demo:

SELECT a.title
     , CASE WHEN b.group_id = a.first_group_id  THEN b.element_name END AS first_group
     , CASE WHEN b.group_id = a.second_group_id THEN b.element_name END AS second_group
FROM   a, b;

Basically, join once to avoid duplication.

But your question leaves a lot of room for interpretation.
Chances are, you have additional constraints. This may be what you really need:

SELECT a.title
     , CASE WHEN b.group_id = a.first_group_id  THEN b.element_name END AS first_group
     , CASE WHEN b.group_id = a.second_group_id THEN b.element_name END AS second_group
FROM   a
LEFT   JOIN b ON b.group_id IN (a.first_group_id, a.second_group_id)
ORDER  BY a.title, b.group_id, element_name;

db<>fiddle here

Plus, you might need an additional column to define order in table B.

And UNION ALL may still be faster, depending on undisclosed details.

---

The reason you get 6 rows with 2x LEFT JOIN in your example:

• Two SQL LEFT JOINS produce incorrect result

It's just the wrong approach for the task.

Answer 2

One neat option to avoid querying the tables twice is to unpivot A first.

You need some CASE expressions because you also want to split out element_name back into different columns.

Not so good at Postgres, I may have the syntax slightly off

SELECT
    A.title,
    CASE WHEN A.first_group_id  = v.group_id THEN B.element_name END AS element_name0,
    CASE WHEN A.second_group_id = v.group_id THEN B.element_name END AS element_name1
FROM A
INNER JOIN LATERAL (VALUES
    (A.first_group_id),
    (A.second_group_id)
) AS v(group_id)
LEFT JOIN B ON v.group_id = B.group_id;