a minus b, how?

Hi, I know that under oracle you can do simple minus calculations of set
data extracted via select statements i.e.
select client from tableA
minus
select client from tableB
But how do ytou do this in sql?
I tried two methods:
method 1:
select a.client from tableA a where a.client not in (select b.client from
tableB b)
... this doesnt return any results
method 2:
select a.client, b.client from tableA a left join tableB b on a.client =
b.client
.. those results with b.client= null are the ones im interested in
I've noticed that method 1 and method 2 dont give the same results. method 1
gives no results, method 2 gives > 0 results. Whats the difference between
method 1 and 2, and my objective of setA - setB ?
any help most appreciated!
cheers, johnjohn r wrote:
> Hi, I know that under oracle you can do simple minus calculations of set
> data extracted via select statements i.e.
> select client from tableA
> minus
> select client from tableB
> But how do ytou do this in sql?
> I tried two methods:
> method 1:
> select a.client from tableA a where a.client not in (select b.client from
> tableB b)
> ... this doesnt return any results
> method 2:
> select a.client, b.client from tableA a left join tableB b on a.client =
> b.client
> .. those results with b.client= null are the ones im interested in
> I've noticed that method 1 and method 2 dont give the same results. method
1
> gives no results, method 2 gives > 0 results. Whats the difference between
> method 1 and 2, and my objective of setA - setB ?
> any help most appreciated!
> cheers, john
In SQL Server 2005 use EXCEPT instead of Oracle's MINUS.
In SQL Server 2000 use either the NOT IN method or your LEFT JOIN
method (add WHERE b.client IS NULL) or use NOT EXISTS.
The difference between your NOT IN query and your LEFT JOIN query is
almost certainly to do with NULLs in TableB. If a client in the
subquery is NULL then the result of NOT IN is always false or unknown
so no rows will be returned. Change your first query to:
SELECT A.client
FROM tableA A
WHERE A.client NOT IN
(SELECT B.client
FROM tableB AS B
WHERE B.client IS NOT NULL) ;
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1132222310.964234.264250@.o13g2000cwo.googlegroups.com...
> john r wrote:
> In SQL Server 2005 use EXCEPT instead of Oracle's MINUS.
> In SQL Server 2000 use either the NOT IN method or your LEFT JOIN
> method (add WHERE b.client IS NULL) or use NOT EXISTS.
> The difference between your NOT IN query and your LEFT JOIN query is
> almost certainly to do with NULLs in TableB. If a client in the
> subquery is NULL then the result of NOT IN is always false or unknown
> so no rows will be returned. Change your first query to:
> SELECT A.client
> FROM tableA A
> WHERE A.client NOT IN
> (SELECT B.client
> FROM tableB AS B
> WHERE B.client IS NOT NULL) ;
> --
> David Portas
> SQL Server MVP
> --
>
thanks for the reply, all the info is much appreciated!
cheers, john