OraFAQ Forum: SQL & PL/SQL » Understand Difference between queries

Home » SQL & PL/SQL » SQL & PL/SQL » Understand Difference between queries

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Understand Difference between queries [message #643221]

Thu, 01 October 2015 06:03

purnima1
Messages: 79
Registered: June 2014

Member

Hi All,
Issue is to find cumulative sum
From the given below I just want to understand what is exact difference between two queries when both are returning same result .
I have implemented first solution .how it is different from second


create table cumulative_test (empid number,salary number ) ;
insert into cumulative_test values (1000,2000);
insert into cumulative_test values (4000,9000);
insert into cumulative_test values (6000,2000);
insert into cumulative_test values (3000,4000);

commit;


select empid,salary,sum(salary)over   (order by empid ) cum_sum from cumulative_test;
select empid,salary,sum(salary)over   (order by empid ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) cum_sum from cumulative_test;

result is :
empid salary cum_sum
1000 2000 2000
3000 4000 6000
4000 9000 15000
6000 2000 17000

Report message to a moderator

Re: Understand Difference between queries [message #643222 is a reply to message #643221]

Thu, 01 October 2015 06:14

John Watson
Messages: 8929
Registered: January 2010
Location: Global Village

Senior Member

No difference: they are equivalent SQLs. Generate execution plans for them, and you'll see that they are the same.

It is like asking the difference between these:

select ename from emp natural join dept where dname='SALES';
select ename from emp where deptno in (select deptno from dept where dname='SALES');

Report message to a moderator

Re: Understand Difference between queries [message #643226 is a reply to message #643221]

Thu, 01 October 2015 06:45

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

From the doc:

Quote:

If you omit the windowing_clause entirely, then the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

So the difference is between RANGE and ROWS.
Here's an example of this difference:

SQL> select deptno, sal, sum(sal) over(order by deptno) sum_sal from emp;
    DEPTNO        SAL    SUM_SAL
---------- ---------- ----------
        10       2450       8750
        10       5000       8750
        10       1300       8750
        20       2975      19625
        20       3000      19625
        20       1100      19625
        20        800      19625
        20       3000      19625
        30       1250      29025
        30       1500      29025
        30       1600      29025
        30        950      29025
        30       2850      29025
        30       1250      29025

14 rows selected.

SQL> select deptno, sal, sum(sal) over(order by deptno ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sum_sal from emp;
    DEPTNO        SAL    SUM_SAL
---------- ---------- ----------
        10       2450       2450
        10       5000       7450
        10       1300       8750
        20       2975      11725
        20       3000      14725
        20       1100      15825
        20        800      16625
        20       3000      19625
        30       1250      20875
        30       1500      22375
        30       1600      23975
        30        950      24925
        30       2850      27775
        30       1250      29025

14 rows selected.

[Updated on: Thu, 01 October 2015 07:02]

Report message to a moderator