[thelist] Weird SQL problem with INTERSECT and UNION

rudy r937 at interlog.com
Wed Apr 2 07:06:40 CST 2003

hi ted

> Think I figured out my own question. Seems like MS SQL does
> not support INTERSECT, which is quite odd.

yeah, imagine that, microsoft not supporting a globally accepted standard

> Also, I figured out if I combine my queries on the cost,
> and just AND the costs, instead of using INTERSECT,
> it works great and the queries are much more simpler.

i woulda gone further and just ditched the first of the two
queries in the union --

  SELECT ...
   WHERE C.pid=1 AND C.cost > 0.00 ...

  SELECT ...
   WHERE C.pid=1 AND C.cost > 10   ...

i cannot stress enough the importance, if you wish to be successful
with sql (or any other computer language for that matter), of
stepping back and taking a critical look at what you're doing

when you INTERSECT those two queries, you are asking for
only those rows which are in both sets

obviously, if a row satisfies the second query, the cost will be
greater than 10, and, by the application of basic arithmetic principles,
it will, therefore, also be greater than zero


> Still odd that MS SQL does not support INTERSECT.
> Is there any work arounds for this or hacks to emulate it?

"hacks" may indeed be an apt description

the page that jason linked to gives quite inadequate examples
of how to achieve equivalence with EXISTS

it is not enough to test for existence of the primary key
in the subquery, it is necessary to test that *all* columns
have matching values

this situation is analagous to the processing required for the DISTINCT

  select distinct foo, bar, qux ...

DISTINCT detects duplicate rows, and a common mistake is to
think that it applies only to the column which directly
follows it in the SELECT list

DISTINCT is typically implemented by sorting the intermediate
results on all columns, then comparing successive pairs of
rows on all columns

INTERSECT works much the same way, in that rows from the
two queries have to be identical in all columns

thus, the example given in the page jason linked to is rather inadequate --

   SELECT OrderID, OrderDate
     FROM Orders O
          ( SELECT 1
              FROM RefundsTable R
             WHERE O.OrderID = R.OrderID

this is a good query, but i just cannot see how it could
ever have been an INTERSECT

the only possibility is that the INTERSECT was looking for
orders returned on the same day, but then the above EXISTS
construction does not test for that!!

if any of this is not clear, just holler


More information about the thelist mailing list