SQL> SELECT * FROM TEST3;
NUM NAME NO
--- -- ----------
111 AAA 10
222 BBB 20
333 CCC 30
444 DDD 10
SQL> SELECT * FROM TEST4;
NO PLACE
---------- ----------
10 TOKYO
20 OSAKA
30 HUKA
40 NAGA
SQL> SELECT PLACE FROM TEST4 WHERE NO IN (SELECT NO FROM TEST3);
PLACE
----------
HUKA
OSAKA
TOKYO
SQL> SELECT PLACE FROM TEST4 WHERE NO NOT IN (SELECT NO FROM TEST3);
PLACE
----------
NAGA
SQL> SELECT PLACE FROM TEST4 WHERE NO > (SELECT AVG(NO) FROM TEST3);
PLACE
----------
OSAKA
HUKA
NAGA
|