How can I substitute a left join in SQL

Question

Can anyone tell me how can I write the equivalent of a left join without really using left joins.

Select * from a left join b on a.name = b.name.

Answer 1

Bear in mind that SQL’s outer join is a kind of relational union which is expressly designed to project null values. If you want to avoid using the null value (a good thing, in my opinion), you should avoid using outer joins. Note that modern relational languages have dispensed with the concept of null and outer join entirely.

This outer join:

SELECT DISTINCT T1.id, T1.value, T2.other_value
  FROM T1
       LEFT OUTER JOIN T2
          ON T1.id = T2.id;

…is semantically equivalent to this SQL code:

SELECT T1.id, T1.value, T2.other_value
  FROM T1
       INNER JOIN T2
          ON T1.id = T2.id
UNION
SELECT T1.id, T1.value, NULL
  FROM T1
 WHERE NOT EXISTS (
                   SELECT * 
                     FROM T2
                    WHERE T1.id = T2.id
                  );

The second query may look long winded but that’s only because of the way SQL has been designed/evolved. The above is merely a natural join, a union and a semijoin. However, SQL has no semijoin operator, requires you to specify column lists in the SELECT clause and to write JOIN clauses if your product hasn’t implemented Standard SQL’s NATURAL JOIN syntax, which results in a lot of code to express something quite simple.

Therefore, you could write code such as the second query above but using an actual default value rather than the null value.