OraFAQ Forum: SQL & PL/SQL » Oracle Sub-query and AND clause behaviour

Home » SQL & PL/SQL » SQL & PL/SQL » Oracle Sub-query and AND clause behaviour (11g)

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

Oracle Sub-query and AND clause behaviour [message #666643]

Sun, 19 November 2017 00:01

LKT
Messages: 5
Registered: November 2017

Junior Member

Hi,

I want to understand the difference b/w IN and AND clause in query.

Ex.

Query-1.

      select * from A
        where (x,y) in (Select i,j from B)

Query-2.

      select * from A
        where x in (Select i from B)
            and y in (Select j from B)

Are both queries same ?

Regards,
Lavkush

Report message to a moderator

Re: Oracle Sub-query and AND clause behaviour [message #666644 is a reply to message #666643]

Sun, 19 November 2017 00:27

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

In your first query, the i and j values that match the x and y values must be in the same row. In the second query, the i and j values that correspond to the x and y values can be in any row. Please see the example below.

SCOTT@orcl_12.1.0.2.0> select * from a
  2  /

         X          Y
---------- ----------
         1          2
         3          4

2 rows selected.

SCOTT@orcl_12.1.0.2.0> select * from b
  2  /

         I          J
---------- ----------
         1          2
         3          5
         6          4

3 rows selected.

SCOTT@orcl_12.1.0.2.0> select * from A
  2  	     where  (x,y) in (Select i,j from B)
  3  /

         X          Y
---------- ----------
         1          2

1 row selected.

SCOTT@orcl_12.1.0.2.0> select * from A
  2  	     where x in (Select i from B)
  3  		 and y in (Select j from B)
  4  /

         X          Y
---------- ----------
         1          2
         3          4

2 rows selected.

Report message to a moderator

Re: Oracle Sub-query and AND clause behaviour [message #666645 is a reply to message #666644]

Sun, 19 November 2017 00:34

LKT
Messages: 5
Registered: November 2017

Junior Member

Thanks

Report message to a moderator

Re: Oracle Sub-query and AND clause behaviour [message #666780 is a reply to message #666645]

Fri, 24 November 2017 09:11

Bill B
Messages: 1971
Registered: December 2004

Senior Member

The way it should be written is

select a.*
from b,a
where a.x = b.i
and a.y = b.j;

Report message to a moderator

Previous Topic:	Unable to grant Admin privileges on SQL Developer 12c
Next Topic:	Array ORA-06533: Subscript beyond count

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri May 08 20:49:48 CDT 2026