Home » SQL & PL/SQL » SQL & PL/SQL » IN CLAUSE OUTPUT
IN CLAUSE OUTPUT [message #618206] Wed, 09 July 2014 06:50 Go to next message
anniepeteroracle
Messages: 47
Registered: March 2012
Location: bangalore
Member
SQL> ed
Wrote file afiedt.buf

1* select * from dept where deptno in (30,10,20)
SQL> /

DEPTNO DNAME LOC
---------- -------------- -------------
20 RESEARCH DALLAS
10 ACCOUNTING NEW YORK
30 SALES CHICAGO

SQL> ed
Wrote file afiedt.buf

1* select * from dept where deptno in (10,30,20)
SQL> /

DEPTNO DNAME LOC
---------- -------------- -------------
20 RESEARCH DALLAS
30 SALES CHICAGO
10 ACCOUNTING NEW YORK

SQL> ed
Wrote file afiedt.buf

1* select * from dept where deptno in (30,20,10)
SQL> /

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO

------------------------------------------------------------------------------
HERE THE OUTPUT IS DIFFERENT AS PER IN CLAUSE
IS IT WORKING LIKE LAST IN FAST OUT (LIFO)

Report message to a moderator

Re: IN CLAUSE OUTPUT [message #618209 is a reply to message #618206] Wed, 09 July 2014 07:05 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Without an ORDER BY you cannot depend on the order of rows returned. 

SQL> select * from dept where deptno in (30,10,20) ORDER BY deptno;
DEPTNO DNAME          LOC
------ -------------- -------------
    10 ACCOUNTING     NEW YORK
    20 RESEARCH       DALLAS
    30 SALES          CHICAGO
SQL> select * from dept where deptno in (10,30,20) ORDER BY deptno;
DEPTNO DNAME          LOC
------ -------------- -------------
    10 ACCOUNTING     NEW YORK
    20 RESEARCH       DALLAS
    30 SALES          CHICAGO
SQL> select * from dept where deptno IN (30,20,10) ORDER BY deptno;
DEPTNO DNAME          LOC
------ -------------- -------------
    10 ACCOUNTING     NEW YORK
    20 RESEARCH       DALLAS
    30 SALES          CHICAGO

Report message to a moderator

Re: IN CLAUSE OUTPUT [message #618210 is a reply to message #618206] Wed, 09 July 2014 07:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Do not post in upper case, this means you're shouting and is not appreciated.

Quote:
IS IT WORKING LIKE LAST IN FAST OUT (LIFO)


You can't say how it works.
The ONLY way, I repeat the ONLY way, to have a specific order in the output is to use an ORDER BY clause.

Report message to a moderator

Re: IN CLAUSE OUTPUT [message #618215 is a reply to message #618206] Wed, 09 July 2014 08:08 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
anniepeteroracle wrote on Wed, 09 July 2014 17:20

IS IT WORKING LIKE LAST IN FAST OUT (LIFO)


In addition to what I and Michel said above, see the rows returned by the same query WITHOUT an order by is different for me than yours, however, in all 3 cases I don't get what you said LIFO.

SQL> select * from dept where deptno in (30,10,20)
  2  /
DEPTNO DNAME          LOC
------ -------------- -------------
    10 ACCOUNTING     NEW YORK
    20 RESEARCH       DALLAS
    30 SALES          CHICAGO
SQL> select * from dept where deptno in (10,30,20)
  2  /
DEPTNO DNAME          LOC
------ -------------- -------------
    10 ACCOUNTING     NEW YORK
    20 RESEARCH       DALLAS
    30 SALES          CHICAGO
SQL> select * from dept where deptno in (30,20,10)
  2  /
DEPTNO DNAME          LOC
------ -------------- -------------
    10 ACCOUNTING     NEW YORK
    20 RESEARCH       DALLAS
    30 SALES          CHICAGO


Hence you cannot guarantee the order in which rows are returned, unless you specify ORDER BY.

Report message to a moderator

Re: IN CLAUSE OUTPUT [message #618217 is a reply to message #618215] Wed, 09 July 2014 08:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Yes the order depends on the code that is executed which depends on many things like version, patch, number of rows, indexes, statistics... as I said we cannot know what is done or will be done during the next execution.

Report message to a moderator

Re: IN CLAUSE OUTPUT [message #618249 is a reply to message #618217] Thu, 10 July 2014 00:41 Go to previous messageGo to next message
anniepeteroracle
Messages: 47
Registered: March 2012
Location: bangalore
Member
SQL> desc dept;
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPTNO NOT NULL NUMBER(2)
DNAME NOT NULL VARCHAR2(14)
LOC NOT NULL VARCHAR2(13)

SQL> select * from dept;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

SQL> select * from dept where deptno in (10,20,30);

DEPTNO DNAME LOC
---------- -------------- -------------
30 SALES CHICAGO
20 RESEARCH DALLAS
10 ACCOUNTING NEW YORK

SQL> select * from dept where deptno in (10,20,30,40);

DEPTNO DNAME LOC
---------- -------------- -------------
40 OPERATIONS BOSTON
30 SALES CHICAGO
20 RESEARCH DALLAS
10 ACCOUNTING NEW YORK

here is my table and sql query but with out order by clause .

Report message to a moderator

Re: IN CLAUSE OUTPUT [message #618250 is a reply to message #618249] Thu, 10 July 2014 01:07 Go to previous message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And?

Report message to a moderator

Previous Topic: SQL Group Query
Next Topic: help in sql query
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Apr 26 06:29:47 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.