How to retrieve data from 3 tables joined by a record identifier

join;query

I have 3 tables.

I need to check whether a specific identifier is present in those tables using a specific WHERE condition. I need to fetch data from all 3 tables.

I have tried LEFT JOIN, RIGHT JOIN and FULL OUTER JOIN but I couldn't get the values of the middle table.

My query attempt is:

Select t1.col1, t2.col2, t3.col3
from t1
left outer join t2
on t1.id=t2.id
full outer join t3
on t1.id=t3.id
where t.id= '101';

I couldn't get the values which are present only in t2 table.

Best Answer

I am assuming that the ID column is either the PK or otherwise declared as unique in all three tables.

Now, if each of these scenarios is equally possible:

  • all three tables have a row with the ID of 101,

  • only two (any two) of the three tables have a row with the ID of 101,

  • only one (any one) of the three tables has a row with the ID of 101,

and you want the requested data from every table that has a match – then you need to use only full joins.

This is one way how you could implement the request:

SELECT
  t1.Col AS t1Col,
  t2.Col AS t2Col,
  t3.Col AS t3Col
FROM
  t1
  FULL JOIN t2 ON t1.ID = t2.ID
  FULL JOIN t3 ON COALESCE(t1.ID, t2.ID) = t3.ID
WHERE
  COALESCE(t1.ID, t2.ID, t3.ID) = 101
;

As you can see, in order to match the third table's ID to that of either of the other two tables when only one of them has a match, you can use the COALESCE function. COALESCE will pick a non-empty (non-null) ID of the two specified, thus making sure that the third ID will be matched with an existing row in either table. The same condition will work correctly if the match is present in both tables.

The WHERE condition uses a similar technique for filtering the result set. Since each table's ID in every row of the joined set is supposed to be either the same value or a null, the COALESCE function will necessarily pick one that is not null to compare to the specified argument (101). Thus, only the row that has the matching data from all or any of the three tables will be selected.

You could also take a slightly different approach: separately select the row matching the ID = 101 condition from each table, use the results as derived tables and then join them:

SELECT
  t1.Col AS t1Col,
  t2.Col AS t2Col,
  t3.Col AS t3Col
FROM
  (
    SELECT ID, Col FROM t1 WHERE ID = 101
  ) AS t1
  FULL JOIN
  (
    SELECT ID, Col FROM t2 WHERE ID = 101
  ) AS t2 ON t1.ID = t2.ID
  FULL JOIN
  (
    SELECT ID, Col FROM t3 WHERE ID = 101
  ) AS t3 ON COALESCE(t1.ID, t2.ID) = t3.ID
;

Although you now have to repeat the same filtering condition, the resulting query might turn out to be more efficient – that will depend on how smart the query optimiser is in your database system.

Test both queries to determine which works better for you.

Related Question