Your query will not work because the query compares all rows to all rows which means that when the point intersect 1 point it also disjoint at least most of the polygons in table 2. So you need to work with a subquery in some way. There are many options.
– Nicklas AvénOct 14 '14 at 6:03

SELECT point FROM pointTable INNER JOIN
(SELECT COALESCE(ST_Difference(a.polygon,b.polygon),a.polygon) diffPolygon FROM polygonTableA as a LEFT JOIN polygonTableB as b on ST_Intersects(a.polygon,b.polygon)) poly
ON ST_Intersects(pointTable.point,poly.diffPolygon)

The left join and coalesce is to get the polygons from polygonTableA that doesn't intersect at alm with the other polygon table.

Try intersecting the two polygons first using STIntersection as a sub-query, then run an STIntersects on the points against the Intersection of the two polygons.

Something like this:

select points.geom
, points.OtherFields
from
(
select
p1.geom.STIntersection(p2.geom) as geom
from
dbo.polygonLayer1 as p1
, dbo.polygonLayer2 as p2
) as polyInt --gets you the intersection of the 2 polygon inputs as a sub query
, dbo.pointsLayer as points
where points.geom.STIntersects(polyInt.geom) = 1