EXISTS is different from IN.
EXISTS just checks for the existence of rows, whereas IN checks actual values.
EXISTS typically offers better performance than IN with subqueries.
You should use EXISTS rather than IN wherever possible.
When a list of values contains a null, NOT EXISTS returns true, but NOT IN returns false.
SQL> -- create demo table
SQL> create table Employee(
2 EMPNO NUMBER(3),
3 ENAME VARCHAR2(15 BYTE),
4 HIREDATE DATE,
5 ORIG_SALARY NUMBER(6),
6 CURR_SALARY NUMBER(6),
7 REGION VARCHAR2(1 BYTE),
8 MANAGER_ID NUMBER(3)
9 )
10 /
Table created.
SQL>
SQL> create table job (
2 EMPNO NUMBER(3),
3 jobtitle VARCHAR2(20 BYTE)
4 )
5 /
Table created.
SQL>
SQL> insert into job (EMPNO, Jobtitle) values (1,'Tester');
1 row created.
SQL> insert into job (EMPNO, Jobtitle) values (2,'Accountant');
1 row created.
SQL> insert into job (EMPNO, Jobtitle) values (3,'Developer');
1 row created.
SQL> insert into job (EMPNO, Jobtitle) values (4,'COder');
1 row created.
SQL> insert into job (EMPNO, Jobtitle) values (5,'Director');
1 row created.
SQL> insert into job (EMPNO, Jobtitle) values (6,'Mediator');
1 row created.
SQL> insert into job (EMPNO, Jobtitle) values (7,'Proffessor');
1 row created.
SQL> insert into job (EMPNO, Jobtitle) values (8,'Programmer');
1 row created.
SQL> insert into job (EMPNO, Jobtitle) values (9,'Developer');
1 row created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION, MANAGER_ID)
2 values (1, 'Jason', to_date('19960725','YYYYMMDD'), 1234, 8767, 'E', 2)
3 /
1 row created.
SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION, MANAGER_ID)
2 values (2, 'John', to_date('19970715','YYYYMMDD'), 2341, 3456, 'W', 3)
3 /
1 row created.
SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION, MANAGER_ID)
2 values (3, 'Joe', to_date('19860125','YYYYMMDD'), 4321, 5654, 'E', 3)
3 /
1 row created.
SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION, MANAGER_ID)
2 values (4, 'Tom', to_date('20060913','YYYYMMDD'), 2413, 6787, 'W', 4)
3 /
1 row created.
SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION, MANAGER_ID)
2 values (5, 'Jane', to_date('20050417','YYYYMMDD'), 7654, 4345, 'E', 4)
3 /
1 row created.
SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION, MANAGER_ID)
2 values (6, 'James', to_date('20040718','YYYYMMDD'), 5679, 6546, 'W', 5)
3 /
1 row created.
SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION, MANAGER_ID)
2 values (7, 'Jodd', to_date('20030720','YYYYMMDD'), 5438, 7658, 'E', 6)
3 /
1 row created.
SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (8, 'Joke', to_date('20020101','YYYYMMDD'), 8765, 4543, 'W')
3 /
1 row created.
SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)
2 values (9, 'Jack', to_date('20010829','YYYYMMDD'), 7896, 1232, 'E')
3 /
1 row created.
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
EMPNO ENAME HIREDATE ORIG_SALARY CURR_SALARY R MANAGER_ID
---------- --------------- --------- ----------- ----------- - ----------
1 Jason 25-JUL-96 1234 8767 E 2
2 John 15-JUL-97 2341 3456 W 3
3 Joe 25-JAN-86 4321 5654 E 3
4 Tom 13-SEP-06 2413 6787 W 4
5 Jane 17-APR-05 7654 4345 E 4
6 James 18-JUL-04 5679 6546 W 5
7 Jodd 20-JUL-03 5438 7658 E 6
8 Joke 01-JAN-02 8765 4543 W
9 Jack 29-AUG-01 7896 1232 E
9 rows selected.
SQL> select * from job
2 /
EMPNO JOBTITLE
---------- --------------------
1 Tester
2 Accountant
3 Developer
4 COder
5 Director
6 Mediator
7 Proffessor
8 Programmer
9 Developer
9 rows selected.
SQL>
SQL> SELECT empno, ename
2 FROM employee
3 WHERE NOT EXISTS (null);
WHERE NOT EXISTS (null)
*
ERROR at line 3:
ORA-00928: missing SELECT keyword
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL> drop table job
2 /
Table dropped.