SQL Server SELECT-clause - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SQL Server SELECT-clause

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-11-21 : 07:54:19

karri writes "Hi,

Is there any function in SQL-Server which works like MINUS in Oracle? Here is an example in Oracle:

SELECT a.col1, a.col2, a.col from tab1 a
MINUS SELECT b.col1, b.col2, b.col from tab1 b

Returns onle those rows which are not exacly same in both tables.
How can I do it in SQL Server?

BR Karri"

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-11-21 : 09:00:07

You can use NOT EXISTS

http://vyaskn.tripod.com/oracle_sql_server_differences_equivalents.htm


create table t1 (col1 int identity(0,1), col2 int, col3 int)

insert into t1 (col2,col3)
select 0,0 union all select 1,0 union all select 2,0 union all select 3,3

create table t2 (col1 int identity(0,1), col2 int, col3 int)

insert into t2  (col2,col3)
select 0,0 union all select 2,1 union all select 1,1 union all select 3,3

SELECT col1,col2,col3
FROM t1 a
WHERE NOT EXISTS
(
SELECT 1
FROM t2 b
WHERE a.col1 = b.col1 and a.col2 = b.col2 and a.col3 = b.col3
)

drop table t1
drop table t2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources