SQL and Relational Theory, 2nd Edition

A REMARK ON OUTER JOIN

Outer join is expressly designed to produce nulls in its result and should therefore be avoided, in general. Relationally speaking, it’s a kind of shotgun marriage: It forces tables into a kind of union—yes, I do mean union, not join—even when the tables in question fail to conform to the usual requirements for union (see Chapter 6). It does this, in effect, by padding one or both of the tables with nulls before doing the union, thereby making them conform to those usual requirements after all. But there’s no reason why that padding shouldn’t be done with proper values instead of nulls, as in this example:

     SELECT SNO , PNO FROM SP
     UNION
     SELECT SNO , 'nil' AS PNO FROM S
     WHERE  SNO NOT IN ( SELECT SNO FROM SP )

Result (note the line for supplier S5 in particular):

SNO	PNO
S1	P1
S1	P2
S1	P3
S1	P4
S1	P5
S1	P6
S2	P1
S2	P2
S3	P2
S4	P2
S4	P4
S4	P5
S5	nil

Alternatively, the same result could be obtained by using the explicit SQL outer join operator in conjunction with COALESCE, as here:

     SELECT SNO , COALESCE ( PNO , 'nil' ) AS PNO
     FROM   S NATURAL LEFT OUTER JOIN SP