minus and intersect functionality

[Sumanth Suri]

Hi,
I've used the minus functionality which is available in Oracle and
i would like to use it in SQL server, but i don't know how to. The
folllowing is how it works in Oracle

Select symbols from symbol_table
minus
select tsymbols from trade

It returns a list of all the symbols from symbol_table which are not
present in trade.
Similarly, the intersect will return only those which are common to
both.

I was wondering if someone throw some light on this problem for me.

Thanks in advance,
Sumanth

[David Portas]

A generalised minus query using NOT EXISTS:

SELECT symbol
FROM symbol_table
WHERE NOT EXISTS
(SELECT *
FROM trade
WHERE tsymbol = symbol_table.symbol)

Alternatively, "Minus JOIN", assuming the Symbol column is unique in both
tables:

SELECT S.symbol
FROM symbol_table AS S
LEFT JOIN trade AS T
ON S.symbol = T.tsymbol
AND T.tsymbol IS NULL

Generalised intersection, using EXISTS:

SELECT symbol
FROM symbol_table
WHERE EXISTS
(SELECT *
FROM trade
WHERE tsymbol = symbol_table.symbol)

Or just an INNER JOIN if Symbol is unique in both tables:

SELECT S.symbol
FROM symbol_table AS S
JOIN trade AS T
ON S.symbol = T.tsymbol

--
David Portas
------------
Please reply only to the newsgroup
--