CREATE TABLE INC_STATE (
ADDR SMALLINT NOT NULL, 
ID INTEGER NOT NULL, 
VAL INTEGER NOT NULL, 
CONSTRAINT UNQ_ADDR_ID UNIQUE (ADDR, ID));

SELECT DISTINCT ADDR FROM INC_STATE WHERE (ID = 200 AND VAL = 0) OR (ID = 201 AND VAL = 0);

SELECT DISTINCT ADDR FROM INC_STATE WHERE (ID = 200 AND VAL = 0) AND (ID = 201 AND VAL = 0);

SELECT DISTINCT ADDR FROM INC_STATE d 
WHERE 
     EXISTS (SELECT ADDR FROM INC_STATE e WHERE (e.ID = 200 AND e.VAL = 0) AND e.ADDR = d.ADDR)
     AND 
     EXISTS (SELECT ADDR FROM INC_STATE e WHERE (e.ID = 201 AND e.VAL = 0) AND e.ADDR = d.ADDR);

SELECT DISTINCT ADDR FROM INC_STATE d 
JOIN INC_STATE e ON e.ADDR = d.ADDR AND e.ID = 200 AND e.VAL = 0
WHERE d.ID = 201 AND d.VAL = 0

Statement failed, SQLSTATE = 42702
Dynamic SQL Error
-SQL error code = -204
-Ambiguous field name between table INC_STATE and table INC_STATE
-ADDR

SELECT a.ADDR FROM INC_STATE a 
JOIN INC_STATE b ON a.ADDR=b.ADDR 
WHERE a.ID = 200 AND a.VAL = 0 AND b.ID = 201 AND b.VAL = 0

PLAN JOIN (A NATURAL, B INDEX (UNQ_ADDR_ID))

   ADDR
=======
     17
     16
      0
     -1

Current memory = 616364
Delta memory = -4100
Max memory = 801464
Elapsed time= 0.00 sec
Buffers = 75
Reads = 0
Writes 0
Fetches = 1329

SELECT ADDR 
FROM INC_STATE
WHERE VAL = 0 AND ID IN (200, 201)
GROUP BY ADDR
HAVING COUNT(DISTINCT ID) = 2

GROUP BY ADDR HAVING COUNT(DISTINCT ID) = 2

SELECT DISTINCT ADDR FROM INC_STATE WHERE (ID = 200 AND VAL = 0) OR (ID = 201 AND VAL = 0) GROUP BY ADDR HAVING COUNT(DISTINCT ID) = 2;


PLAN (INC_STATE ORDER UNQ_ADDR_ID)

   ADDR
=======
     -1
      0
     16
     17

Current memory = 617660
Delta memory = 784
Max memory = 801464
Elapsed time= 0.00 sec
Buffers = 75
Reads = 0
Writes 0
Fetches = 1930

Планы один в один.

PLAN (E INDEX (UNQ_ADDR_ID))
PLAN (E INDEX (UNQ_ADDR_ID))
PLAN SORT ((D NATURAL))

PLAN JOIN (A NATURAL, B INDEX (UNQ_ADDR_ID))

PLAN (INC_STATE ORDER UNQ_ADDR_ID)

SQL> SELECT ADDR FROM INC_STATE WHERE VAL = 0 AND ID IN (200, 201) GROUP BY ADDR HAVING COUNT(DISTINCT ID) = 2;

PLAN (INC_STATE ORDER UNQ_ADDR_ID)

   ADDR
=======
     -1
      0
     16
     17

Current memory = 1581048
Delta memory = -8
Max memory = 1712464
Elapsed time= 0.00 sec
Buffers = 75
Reads = 0
Writes 0
Fetches = 1930