SQL Server check if where clause is true for any row

Question

I'm going to select those provinces which intersects any railroad. So I do it like this (Using SQL Spatial):

SELECT * FROM ProvinceTable
WHERE (
       SELECT count(*)
       FROM RailroadTable
       WHERE ProvinceTable.Shape.STIntersects(RailroadTable.Shape) > 1 
      ) > 0

But it is not efficient because it has to check the intersection between every single railroad geometry and province geometry in order to calculate the count. However it is better to stop the where clause as soon as every first intersection detected and there is no need to check others. Here is what I mean:

SELECT * FROM ProvinceTable
WHERE (
       --return true if this is true for any row in the RailroadTable:
       -- "ProvinceTable.Shape.STIntersects(RailroadTable.Shape) > 1" 
      )

So is there a better way to rewrite this query for such a goal?

EDIT Surprisingly This query takes the same time and returns no row:

SELECT * FROM ProvinceTable
WHERE EXISTS (
       SELECT *
       FROM RailroadTable
       WHERE ProvinceTable.Shape.STIntersects(RailroadTable.Shape) > 1 
      )

Can those shape constructs be used in an inner join? Like shown [here](http://stackoverflow.com/a/498760/578411) — rene, Mar 15 '15 at 11:14
I don't think this idea improves the performance because intersection must be check for every single rows using inner join. — Hossein Narimani Rad, Mar 15 '15 at 11:18

score 3 · Accepted Answer · edited Mar 15 '15 at 11:34

3

You want to use exists:

SELECT pt.*
FROM ProvinceTable pt
WHERE EXISTS (SELECT 1
              FROM RailroadTable rt
              WHERE pt.Shape.STIntersects(rt.Shape) = 1 
             );

edited Mar 15 '15 at 11:34

Martin Smith

438,706
87
741
845

answered Mar 15 '15 at 11:20

Gordon Linoff

1,242,037
58
646
786

I checked this method before and surprisingly it does not returns any row! Moreover it takes as much as `count(*)` method takes time to run! – Hossein Narimani Rad Mar 15 '15 at 11:23
1

@HosseinNarimaniRad the check would need to be `=1`. This still can end up testing the condition against them Cartesian product though. Do you have any spatial indexes? – Martin Smith Mar 15 '15 at 11:37
@MartinSmith you are right. No there is no spatial index available. I prefer ed to concentrate on query optimization – Hossein Narimani Rad Mar 15 '15 at 11:39

SQL Server check if where clause is true for any row

1 Answers1