[postgis-users] Strange Left Join behaviour

[Andy Colson]

On 12/29/2014 8:46 AM, Arnaud Vandecasteele wrote:
> Hey all,
>
> It's been a couple of days that I'm trying to compute the difference
> (like the geoprocessing difference with QGIS) between two geometry
> (polygon) tables. These tables (let's call them A and B) contain
> polygons where :
>   - polygon from A can be intersected by one or more polygon from B
>   - polygon from A can have no spatial relationship with polygon from B
> (no intersection)
>
> To realize this difference I've joined the two tables with a "LEFT OUTER
> JOIN" so even if I don't have any intersection between A and B I should
> get the geometry from A.
>
> Below it's an example of the SQL query :
> *********************************
> SELECT  A.id,
>          COALESCE(
>              ST_Difference(
>                  A.the_geom,
>                  ST_Union(B.the_geom)
>              ),
>              A.the_geom
>      ) As the_geom
> FROM A
> LEFT JOIN B ON A.the_geom && B.the_geom
> AND ST_Intersects(A.the_geom, B.the_geom)
> GROUP BY A.id;
> *********************************
>
> The difference between objects from table A that are intersected by one
> or more objects from B is correct. But I don't get the objects from A
> that are not intersected by B. I don't understand why as I use a  "LEFT
> OUTER JOIN" and the COALESCE function.
>
> Could you please tell me what I'm doing wrong or what I don't understand ?
>
> Thanks for your help.
>
> Arnaud
>
>

I'm assuming ST_Intersects will return null if B.the_geom is null.  Try:

LEFT JOIN B ON A.the_geom && B.the_geom
AND (B.the_geom is null or ST_Intersects(A.the_geom, B.the_geom))

-Andy