Home » SQL & PL/SQL » SQL & PL/SQL » Oracle Sub-query and AND clause behaviour (11g)
Oracle Sub-query and AND clause behaviour [message #666643] Sun, 19 November 2017 00:01 Go to next message
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

Re: Oracle Sub-query and AND clause behaviour [message #666644 is a reply to message #666643] Sun, 19 November 2017 00:27 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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.
Re: Oracle Sub-query and AND clause behaviour [message #666645 is a reply to message #666644] Sun, 19 November 2017 00:34 Go to previous messageGo to next message
LKT
Messages: 5
Registered: November 2017
Junior Member
Thanks
Re: Oracle Sub-query and AND clause behaviour [message #666780 is a reply to message #666645] Fri, 24 November 2017 09:11 Go to previous message
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;
Previous Topic: Unable to grant Admin privileges on SQL Developer 12c
Next Topic: Array ORA-06533: Subscript beyond count
Goto Forum:
  


Current Time: Thu Mar 28 07:35:36 CDT 2024