SQL query

[tostay2003]

Hi All,

Its not a question on datastage. Does anyone have any idea to write a SQL query, performing the same operation as MINUS in oracle. because the database which i am using doesnt support MINUS.

Regards

[bcarlson]

For those of us unfamiliar with Oracle, what does the MINUS operation do that would be different from an arithmetic '-' (as in 2 -1 = 1)?

Brad.

[chulett]

It's not arithmetic, it is a set operation, like a UNION or UNION ALL. Take the contents of the first set and remove any content that matches what's in the second set.

[I_Server_Whale]

Hi All,

Its not a question on datastage. Does anyone have any idea to write a SQL query, performing the same operation as MINUS in oracle. because the database which i am using doesnt support MINUS.

Regards

Do you want to know how to implement MINUS using datastage or using SQL. I'm guessing you are wanting to perform the MINUS operation using SQL. If so, could you let us know the database that you are using. That would help.

Thanks,
Naveen.

[bcarlson]

If you are doing DB2, that sounds like an EXCEPT (or EXCEPT ALL for duplicate records).

Code: Select all

select * from table1

except

select * from table2

This will return all the records from result set 1 that do not match in results set 2.

Brad.

[Ultramundane]

If MsSQL or Sybase, which I do not believe support MINUS or INTERSECT through such elegant words, you can use an EXISTS (INTERSECT) or NOT EXISTS (MINUS) subquery.

Example:
TABLEA (C1, C2, C3, C4)
TABLEB (C1, C2, C3, C4)

MINUS subquery:

Code: Select all

SELECT *
   FROM TABLEA A
 WHERE NOT EXISTS ( SELECT 1
                                    FROM TABLEB B
                                  WHERE A.C1 = B.C1
                                      AND A.C2 = B.C2
                                      AND A.C3 = B.C3
                                      AND A.C4 = B.C4 )

INTERSECT subquery:

Code: Select all

SELECT *
   FROM TABLEA A
 WHERE EXISTS ( SELECT 1
                             FROM TABLEB B
                           WHERE A.C1 = B.C1
                               AND A.C2 = B.C2
                               AND A.C3 = B.C3
                               AND A.C4 = B.C4 )

[tostay2003]

its a new database called netezza. it doesnt support EXCEPT as well. I wrote a correlated subquery (using NOT IN) but the system gets hanged .

are there any other ways to get it.

[Ultramundane]

This is ugly and potentially dangerous.

Code: Select all

SELECT A.*
  FROM TABLEA A,
       TABLEB B
 WHERE A.C1 != B.C1
    OR A.C2 != B.C2
    OR A.C3 != B.C3
    OR A.C4 != B.C4
 GROUP
    BY A.C1,
       A.C2,
       A.C3,
       A.C4
HAVING COUNT(*) = MAX(COUNT(*))       

[Ultramundane]

This is ugly and potentially dangerous.

It also has a bug where the incorrect result could be returned if there is nothing common. This version is more correct, however even uglier. In addition, both sets should be unique across all columns.

Code: Select all

SELECT A.*
  FROM TABLEA A,
       TABLEB B
 WHERE A.C1 != B.C1
    OR A.C2 != B.C2
    OR A.C3 != B.C3
    OR A.C4 != B.C4
 GROUP
    BY A.C1,
       A.C2,
       A.C3,
       A.C4
HAVING COUNT(*) = ( SELECT COUNT(*) FROM TABLEB )

[ray.wurlod]

Some databases support this as DIFFERENCE. There are two possible differences between two tables, A DIFFERENCE B is everything from A that is not in B (that is, not in A INTERSECTION B), while B DIFFERENCE A you can work out.