469,329 Members | 1,364 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,329 developers. It's quick & easy.

minus and intersect functionality

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
Jul 19 '05 #1
1 16469
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
--
Jul 19 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by Nick | last post: by
1 post views Thread by Sumanth Suri | last post: by
1 post views Thread by =?Utf-8?B?TWl0Y2hX?= | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by haryvincent176 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.