[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
;o)
> 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
keyword
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
WHERE EXISTS
( 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
rudy
More information about the thelist
mailing list