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):
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
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