Oracle MINUS equivalent in MSSQL2000 ?

Hello,
has somebody a tip how to make the Oracle-MINUS with SQL Server?
thanks,
Helmuthelmut woess wrote:

> Hello,
> has somebody a tip how to make the Oracle-MINUS with SQL Server?
> thanks,
> Helmut
Typically you can use NOT EXISTS or an outer join. Examples:
SELECT col1, col2
FROM TableA AS A
WHERE NOT EXISTS
(SELECT *
FROM TableB AS B
WHERE B.col1 = A.col1
AND B.col2 = A.col2) ;
SELECT A.col1, A.col2
FROM TableA AS A
LEFT JOIN TableB AS B
ON B.col1 = A.col1
AND B.col2 = A.col2
WHERE B.col1 IS NULL ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Minus Not supported in Server 2000,
Use NOT EXISTS clause in your SELECT statement to generate the same
result.
The following example illustrates the simulation of Oracle's MINUS
operator:
SELECT OrderID, OrderDate
FROM Orders O
WHERE NOT EXISTS
(
SELECT 1
FROM RefundsTable R
WHERE O.OrderID = R.OrderID
)
Thanks,
Sree|||Thanks, but this is notusable for my purpose (i should give more
information in my questions, sorry):
the user can build a query maybe with one field or with 20 fields, maybe
only from one table, maybe from two joined tables. And there is only AND or
OR possible for building this query, so a NOT EXISTS or a NOT IN is not
possible in the query.
For example:
the user wants all customers without a contract of type 'XX'
He can only build a statement
'show me all customers with contract of type XX'
and then set a flag, that he wants to see the MINUS result.
So i need to do a 'select all customers' MINUS 'select all customers which
has at minimum one contract of type XX'
thanks.
Helmut|||Thanks for your answer, but my problem is more complex, so i can't use NOT
EXISTS or NOT IN (see my answer to David Portas).
Helmut|||helmut woess wrote:

> Thanks, but this is notusable for my purpose (i should give more
> information in my questions, sorry):
> the user can build a query maybe with one field or with 20 fields, maybe
> only from one table, maybe from two joined tables. And there is only AND o
r
> OR possible for building this query, so a NOT EXISTS or a NOT IN is not
> possible in the query.
> For example:
> the user wants all customers without a contract of type 'XX'
> He can only build a statement
> 'show me all customers with contract of type XX'
> and then set a flag, that he wants to see the MINUS result.
> So i need to do a 'select all customers' MINUS 'select all customers which
> has at minimum one contract of type XX'
> thanks.
> Helmut
I don't understand your requirements but here's another example that
may help. Inevitably to do this you will require a join in some form.
If EXISTS or OUTER JOIN don't suit you then maybe you could use UNION:
/* DDL and sample data: */
CREATE TABLE A (col1 INTEGER NOT NULL, col2 INTEGER NOT NULL, PRIMARY
KEY (col1,col2));
CREATE TABLE B (col1 INTEGER NOT NULL, col2 INTEGER NOT NULL, PRIMARY
KEY (col1,col2));
INSERT INTO A (col1,col2)
SELECT 1,1 UNION ALL
SELECT 2,2 UNION ALL
SELECT 3,3 ;
INSERT INTO B (col1,col2)
SELECT 2,2 ;
/* A minus B */
SELECT col1, col2
FROM
(SELECT 1 AS x, col1, col2
FROM A
UNION ALL
SELECT 2 AS x, col1, col2
FROM B) AS T
GROUP BY col1, col2
HAVING MAX(x)=1 ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Yes, i see what you mean, this is a very good idea!
Now i have to read the column information, build the statement and execute
it - that should work, i hope :-).
thank you very much,
Helmut
If you are interested in the result i can post it here, as soon as it is
working.|||whenever I want to compare 2 tables, I miss Oracle's MINUS operator.
Here is my way around:
-- rows in table1 missing in table2
select count(*) from table1 t
where not exists(select * from table2 t2
where t2.PK = t.PK)
-- rows present in both tables but different
select count(*) from table1 t
where exists(select * from table2 t2
where t2.PK = t.PK )
and
(select count(*) from
(
select * from table1 t1
union
select * from table2 t2
)t_both where t_both.PK = t.PK
) = 2
This way I don't need to write GROUP BY col1, col2, which may be
annoying for 300 columns.|||In SQL 2005 you can use except and intersect operator which works what
you want to do.|||Yes, if you have a PK and you can use EXISTS and UNION, then this is a good
solution. But my problem is, that the user has a simple query tool without
this features. He can select the fields he wants but he doesn't have EXISTS
or NOT IN.
So let's say you have a table with persons, a table with products and a
table personProduct (person bought this product).
The user now wants all persons who don't own a hammer. But with this query
tool he can't build something like
select name from persons where not exists (select * from personproduct
pp where pp.personid = persons.id and pp.product = 'hammer')
he can only build a
select name from personproduct pp left join persons on persons.id =
pp.personid and pp.product = 'hammer'
and this is exactly what he does NOT want to see. But he can set a flag
that he wants to see the direct opposite, which is
select name from personproduct pp left join persons on persons.id =
pp.personid
MINUS
select name from personproduct pp left join persons on persons.id =
pp.personid and pp.product = 'hammer'
... that's what i want to solve
thanks,
Helmut