Home » SQL & PL/SQL » SQL & PL/SQL » Exists/Not Exists in the same query (Oracle 10g)
Exists/Not Exists in the same query [message #357474] Wed, 05 November 2008 09:03 Go to next message
palhello
Messages: 15
Registered: November 2008
Junior Member
I have the following query

Select
Sum(a.col1),
sum(a.col1)
FROM
Bigtable1 a
WHERE
NOT EXISTS ( SELECT 1 FROM Bigtable2 b where a.id = b.id)
UNION ALL
Select
Sum(a.col1),
sum(a.col1)
FROM
Bigtable1 a
WHERE
EXISTS ( SELECT 1 FROM Bigtable2 b where a.id = b.id);

Can this result be achieved in a single SQL statement possibly with a group by, analytic functions or any other means ?


Hitting two Select on BigTables is proving to be a bit costly.

Thanks

[Updated on: Wed, 05 November 2008 09:08]

Report message to a moderator

Re: Exists/Not Exists in the same query [message #357475 is a reply to message #357474] Wed, 05 November 2008 09:32 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
All you need is an outer join with the big table and have a case statement in the sum clause. Try to come up with a select statement and I am sure somebody will be able to help you.

Regards

Raj
Re: Exists/Not Exists in the same query [message #357481 is a reply to message #357474] Wed, 05 November 2008 10:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You should:
1/ Post an explaination of what you want to get with this query and not expecting we will reverse engineer it.
2/ Read read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.
3/ Post your Oracle version (4 decimals)
4/ Read How to Identify Performance Problem and Bottleneck and provide the requested information.

Regards
Michel
Re: Exists/Not Exists in the same query [message #357498 is a reply to message #357481] Wed, 05 November 2008 11:30 Go to previous messageGo to next message
palhello
Messages: 15
Registered: November 2008
Junior Member
Here is what I got

CREATE TABLE yyy AS 
SELECT	1 id,100 paidamt FROM Dual
UNION ALL 
SELECT	2 id,200 paidamt FROM Dual
UNION ALL
SELECT	3 id,300 paidamt FROM Dual
UNION ALL
SELECT	4 id,400 paidamt FROM Dual
UNION ALL
SELECT	5 id,500 paidamt FROM Dual;
UNION ALL
SELECT	6 id,100 paidamt FROM Dual
UNION ALL 
SELECT	7 id,200 paidamt FROM Dual
UNION ALL
SELECT	8 id,300 paidamt FROM Dual
UNION ALL
SELECT	9 id,400 paidamt FROM Dual
UNION ALL
SELECT	10 id,500 paidamt FROM Dual;

CREATE TABLE zzz AS
SELECT 1 id,3 code FROM dual
UNION ALL
SELECT 2 id,4 code FROM DUAL
UNION ALL
SELECT 2 id,1 code FROM DUAL
UNION ALL     
SELECT 2 id,2 code FROM DUAL
UNION ALL
SELECT 6 id,91 code FROM DUAL;

Select * from yyy;
Select * from zzz;


SELECT 
	Sum(PaidAmt)
FROM
	yyy a    
WHERE
NOT EXISTS ( SELECT 1 FROM zzz c WHERE a.id = c.id AND code IN (4,1,2)) 
UNION ALL
SELECT 
	Sum(PaidAmt)
FROM
	yyy a    
WHERE
EXISTS ( SELECT 1 FROM zzz c WHERE a.id = c.id AND code IN (4,1,2))
; 

Output:
--2800
--200


I wonder if there is some analytic query that we might use in Oracle 10g, to fetch the same output in a single Select. If I use a outer join I get Sum(PaidAmt) as more. I only want to sum the paidamt when the code is 4,1 or 2.

SELECT *
FROM   yyy a
FULL OUTER JOIN zzz b
         ON a.Id = b.Id;


The business logic that I am working on is similar to this.

Thanks, any help would be much appreciated.






Re: Exists/Not Exists in the same query [message #357499 is a reply to message #357498] Wed, 05 November 2008 11:47 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You're nearly there with this:
SELECT *
FROM   yyy a
FULL OUTER JOIN zzz b
         ON a.Id = b.Id;


Now, if a record exists in ZZZ, then b.id is not null.
If a record doesn't exist in ZZZ, then b.is is null.

How would you use CASE and SUM to get two different totals from here?
Re: Exists/Not Exists in the same query [message #357553 is a reply to message #357499] Wed, 05 November 2008 18:39 Go to previous messageGo to next message
palhello
Messages: 15
Registered: November 2008
Junior Member
Well, it's not just about b.id and c.id.
Here is the result of the outer join.
ID PAIDAMT     ID   CODE
 1     100      1      3
 2     200      2      4
 2     200      2      1
 2     200      2      2
 6     100      6     91
 5     500              
 8     300              
 3     300              
10     500              
 7     200              
 4     400              
 9     400     


If I use a code IN (4,1,2) inside the case then due to the outer join I will have a sum of 200+200+200 = 600, whereas I only want the total to be 200 as it is associated with the a single a.id = b.id = 2.

[Updated on: Wed, 05 November 2008 18:40]

Report message to a moderator

Re: Exists/Not Exists in the same query [message #357621 is a reply to message #357553] Thu, 06 November 2008 01:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Something like:
SQL> select sum(PAIDAMT)
  2  from yyy a
  3       left outer join
  4       (select distinct id from zzz where code in (4,1,2)) b
  5       on (a.id = b.id)
  6  group by decode(a.id,b.id,1)
  7  /
SUM(PAIDAMT)
------------
         200
        2800

2 rows selected.

But depending on your data, it will not be faster than the union of 2 queries.

Regards
Michel
Re: Exists/Not Exists in the same query [message #357639 is a reply to message #357553] Thu, 06 November 2008 02:12 Go to previous messageGo to next message
hrishy
Messages: 25
Registered: August 2005
Junior Member
Hi

Can you try this i havent tested it

select case 
	when b.id is null
		then sum(a.first_paidamt)
	else 
		sum(a.first_paidamt)
	end paidamt
from 	(select a.id,first_vlaue(a.paidamt)
		over(partition by a.id 
			order by a.paidtamt) first_paidamt
		from yyy a) a
full outer join 
	(select b.id,b.code
		from zzz b) b
where a.id=b.id 
Re: Exists/Not Exists in the same query [message #357656 is a reply to message #357639] Thu, 06 November 2008 02:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select case 
  2   when b.id is null
  3    then sum(a.first_paidamt)
  4   else 
  5    sum(a.first_paidamt)
  6   end paidamt
  7  from  (select a.id,first_vlaue(a.paidamt)
  8    over(partition by a.id 
  9     order by a.paidtamt) first_paidamt
 10    from yyy a) a
 11  full outer join 
 12   (select b.id,b.code
 13    from zzz b) b
 14  where a.id=b.id 
 15  /
where a.id=b.id
*
ERROR at line 14:
ORA-00905: missing keyword

Regards
Michel
Re: Exists/Not Exists in the same query [message #357679 is a reply to message #357656] Thu, 06 November 2008 04:15 Go to previous messageGo to next message
hrishy
Messages: 25
Registered: August 2005
Junior Member
Hi Micheal

I am sorry i just posted that quickly i don't have oracle acesses at the moment to test.

I would use your query anyways (assuming both the queries form equally well) even if my query works after the syntax correction as it is elegant and simple

regards
Hrishy

[Updated on: Thu, 06 November 2008 04:16]

Report message to a moderator

Re: Exists/Not Exists in the same query [message #357788 is a reply to message #357679] Thu, 06 November 2008 10:01 Go to previous messageGo to next message
palhello
Messages: 15
Registered: November 2008
Junior Member
Quote:

But depending on your data, it will not be faster than the union of 2 queries.



So if the table zzz is pretty big (around 1 million rows), then would using "distinct" be a bad idea against the two queries with Exist and Not Exist ?

Re: Exists/Not Exists in the same query [message #357791 is a reply to message #357788] Thu, 06 November 2008 10:19 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes for big (but 1 million is not big in this time).

Regards
Michel
Previous Topic: Automate Store Proc Testing
Next Topic: script to create synonym for all table of schema
Goto Forum:
  


Current Time: Sat Dec 03 00:56:20 CST 2016

Total time taken to generate the page: 0.13007 seconds