Combining SQL outer joins with inner joins

For the occasions where You really don't want to write explicit sub-queries

 Posted on April 23, 2021

When You have 3 tables in an SQL query, and You need all records from table1 t1, but records from table2 t2 and table3 t3 only if there is a record on both tables t2 and t3, You have some options:

  • Write all outer joins with a WHERE clauses
select t1.field1, t2.field2, t3.field3
from 
  table1 t1 
  left outer join table2 t2 on t1.field1 = t2.field1
  left outer join table3 t3 on t2.field2 = t3.field2
where
  t3.field3 is not null
  • Write sub-query
select t1.field1, t_.field2, t_.field3
from
  table1 t1
  left outer join (
    select t2.field1, t2.field2, t3.field3
    from
      table2 t2
      join table3 t3 on t2.field2 = t3.field2
    where
      t3.field3 is not null
  ) t_ on t1.field1 = t_.field1

In my case, I had to have exactly 1 record returned, that was filtered on t1.

So outer joins with a specific WHERE clause with NOT NULL expressions would not work, as it could return no rows, if no rows in t3 matched my requirements.

I tried using outer join for t2 but inner join for t3

select t1.field1, t2.field2, t3.field3
from 
  table1 t1 
  left outer join table2 t2 on t1.field1 = t2.field1
  join table3 t3 on t2.field2 = t3.field2
where
  t3.field3 is not null

But this would still result in no rows if there were no matching rows in t3 (silly me)

While searching, I stumbled upon a thread on StackOverflow, with a similar question, and I noticed a strange syntax for the joins:

select t1.field1, t2.field2, t3.field3
from 
  table1 t1 
  left outer join (
    table2 t2 join table3 t3 on t2.field2 = t3.field2
  ) t_ on t1.field1 = t_.field1

As it turns out, this is a shorthand syntax for a sub-query - it just omits the SELECT * FROM part.

If we test the same query, rewritten to use a sub-query

select t1.field1, t2.field2, t3.field3
from 
  table1 t1 
  left outer join (
    select *
    from table2 t2 join table3 t3 on t2.field2 = t3.field2
  ) t_ on t1.field1 = t_.field1

We get the same result.

When looking for a more detailed write up on this, I could not find any. All I could find is a mention of this in PostgreSQL (I use mostly PostgreSQL for all my projects) docs, when talking about “Table and Column Aliases”.

If You know where I can read more about this construct, please, let Me know.

sql