I almost never use EXCEPT. I'm going to follow Adam's lead here and look for opportunities to do that in future. I would instinctively do this. It's not as pretty as EXCEPT, unless you have a particular fondness for NULLs. And there's every chance that it's slightly slower. SELECT [D].[ID] FROM (SELECT [D].[ID], [E].[ID] FROM [Parcels] AS [D] LEFT JOIN [Buildings] AS [E] ON Contains([D].[ID], Centroid([E].[ID])) ) WHERE [E].[ID] IS NULL; I'd like to unpack Adam's (the query, as written, does something else entirely, that's why it takes so long to run)
a little bit. Take Yves' first query (in post 1). --sql select P1.id from Parcels as P1, Buildings where contains (P1.id,centroid(Buildings.id)) What this says logically, in bald English, is: The left set is the objects in Parcels The right set is the objects in Buildings For every element (X) in the left set: For every element (Y) in the right set: If X contains Y, list (X, Y) Give me all the X values in that list Now take Yves' second query. --sql select P1.id from Parcels as P1, Buildings where NOT contains (P1.id,centroid(Buildings.id)) In English: The left set is the objects in Parcels The right set is the objects in Buildings For every element (X) in the left set: For every element (Y) in the right set: If X does not contain Y, list (X, Y) Give me all the X values in that list Usually, the list for the second query is much, much smaller than the list for the first. Why is the first query faster so much than the second? That's not the same thing. I'm not smart enough to give a proper answer to that. (I think: spatial indexes.) Anyway, what is required here is "something else entirely", as Adam puts it: list each X which contains no Y. That's equivalent to: list each X which contains a Y, then invert the list (list only the other Xs). (That could be rephrased as: list each X for which the set of of Ys that it does not contain is the all of the Ys.) That's completely different from: for each X, list every Y that it does not contain, then throw away the Ys (leaving multiple repeated Xs).
|