SQL query
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 97
- Joined: Tue Feb 21, 2006 6:45 am
SQL query
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
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
-
- Premium Member
- Posts: 1255
- Joined: Wed Feb 02, 2005 11:54 am
- Location: United States of America
Re: SQL query
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.tostay2003 wrote: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
Thanks,
Naveen.
Anything that won't sell, I don't want to invent. Its sale is proof of utility, and utility is success.
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
If you are doing DB2, that sounds like an EXCEPT (or EXCEPT ALL for duplicate records).
This will return all the records from result set 1 that do not match in results set 2.
Brad.
Code: Select all
select * from table1
except
select * from table2
Brad.
-
- Participant
- Posts: 407
- Joined: Mon Jun 27, 2005 8:54 am
- Location: Walker, Michigan
- Contact:
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:
INTERSECT 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 )
-
- Participant
- Posts: 97
- Joined: Tue Feb 21, 2006 6:45 am
-
- Participant
- Posts: 407
- Joined: Mon Jun 27, 2005 8:54 am
- Location: Walker, Michigan
- Contact:
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(*))
-
- Participant
- Posts: 407
- Joined: Mon Jun 27, 2005 8:54 am
- Location: Walker, Michigan
- Contact:
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.Ultramundane wrote: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(*) = ( SELECT COUNT(*) FROM TABLEB )
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.