Difference between two layers in PostGIS

Question

What is the right way to calculate the difference between two layers? I tried to use the following approach:

SELECT ST_Difference(river.geom, lakes.geom) 
FROM river LEFT JOIN lakes ON ST_Intersects(river.geom, lakes.geom) 

But in the output, I lose the geometries of the river layer which do not intersect any geometries in lakes. It looks like left join does not work as expected.

Currently, I'm using another approach, but I'm not sure this is correct:

SELECT ST_Difference(river.geom, lakes.geom) 
FROM river JOIN lakes ON ST_Intersects(river.geom, lakes.geom) 
UNION 
SELECT river.geom 
FROM river JOIN lakes ON NOT ST_Intersects(river.geom, lakes.geom) 

Answer 1

Just do this:

SELECT COALESCE(ST_Difference(river.geom, lakes.geom), river.geom) As river_geom 
FROM river LEFT JOIN lakes ON ST_Intersects(river.geom, lakes.geom);

reverse

SELECT COALESCE(ST_Difference(river.geom, lakes.geom), lakes.geom) As lake_geom 
FROM lakes LEFT JOIN river ON ST_Intersects(river.geom, lakes.geom);

That is what COALESCE exists for. I much prefer keeping semantics of PostGIS the way they are. It's consistent with relational DB accepted technology and if we make consessions for this, we need to do it for all functions and then the results will be unpredictable.

Answer 2

The problem here is not the left join, that is working as expected. But when the query gets to a river that doesn't intersect with a lake it will feed the ST_Difference function with NULL as the second argument which seems to return null.

/Nicklas