Re: intersect function

From: Vishal Parkar (REMOVE_THIS_vgparkar_at_yahoo.co.in)
Date: 02/20/04


Date: Fri, 20 Feb 2004 15:16:01 +0530

hi burke,

I assume you are looking for similar INTERSECT clause of oracle. INTERSECT is not supported in
SQL Server. The alternate option with you would be to use EXISTS in your query. Intersect
clause in oracle will give you the common rows out of 2 SELECT clauses. so following query
will give you the common rows between the 2 selects.

 select id, status, member_type
 from imis.dbo.name n
 where member_record = 1
 intersect
 select id, status, member_type
 from imis_2003_q4.dbo.name n
 where member_record = 1

In SQL Server 2000 you can use EXISTS clause to do this, your query can be rewritten as:

 select id, status, member_type
 from imis.dbo.name x
 where member_record = 1
 and exists
 ( select id, status, member_type
   from imis_2003_q4.dbo.name n
   where member_record = 1
   and n.id = x.id and n.status = x.status and n.member_type = x.member_type)

-- 
Vishal Parkar
vgparkar@yahoo.co.in


Relevant Pages

  • Re: How top actually works
    ... So the next one shows that order by clause has affected the result set ... sort these N rows according to my order by clause. ... Ie. you are telling SQL Server ... This is much the same as with rownum in Oracle, ...
    (comp.databases.ms-sqlserver)
  • Re: Performance: Conditions in WHERE clause vs. conditions in INNER JOIN?
    ... inclusion of the test for NULLs in an outer join with the conditions ... The special notation in Oracle is basically ... I think SQL server tends to carry the "just give the users the ... join conditions in the WHERE clause. ...
    (comp.databases.ms-sqlserver)
  • Re: UPDATE problem - FROM Clause not supported in Oracle
    ... > I want to have a single statement to work on both sql server and ... > But it seems that the FROM clause is not supported in Oracle, ... SQL Server MVP ...
    (comp.databases.oracle.server)
  • Re: SELECT COUNT(*)
    ... select GetDatewithout the from clause. ... In Oracle, the from is always required so if you used their ... I'd guess SQL Server and Sybase implement ... >Mike Epprecht, Microsoft SQL Server MVP ...
    (microsoft.public.sqlserver.server)
  • Re: Box query
    ... Changing the having clause won't fix a problem that already happened ... I'm changing the aggregation expression. ... intersect is a simple expression ...
    (comp.databases.theory)