Hi,
I will explain EXISTS Operator in Oracle SQL in this post of Oracle SQL Tutorial series.
Read the previous post of this tutorial series before this.
The EXISTS Operator
The EXISTS operator is used to check if existence of any record in a subquery.
The result of this operator is TRUE or FALSE.
The NOT EXISTS Operator
The NOT EXISTS operator is just like EXISTS but it is REVERSE of EXISTS.
The result of this operator is TRUE or FALSE.
The EXISTS Syntax
EXISTS syntax is as follows.
SELECT column1, column2, column3 FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name );
SELECT column_name(s) FROM table_name WHERE EXISTS (SELECT column_name FROM table_name WHERE condition);
The NOT EXISTS Syntax
EXISTS syntax is as follows.
SELECT column1, column2, column3 FROM table_name
WHERE NOT EXISTS
(SELECT column_name FROM table_name );
SELECT column_name(s) FROM table_name WHERE NOT EXISTS (SELECT column_name FROM table_name WHERE condition);
SQL> select first_name,last_name,salary from hr.employees where department_id in (20,30,40) and EXISTS ( select department_id from hr.employees where department_id=10); FIRST_NAME LAST_NAME SALARY -------------------- ------------------------- ---------- Michael Hartstein 14000 Pat Fay 7000 Den Raphaely 12000 Alexander Khoo 4100 Shelli Baida 3900 Sigal Tobias 3800 Guy Himuro 3600 Karen Colmenares 3500 Susan Mavris 7500 9 rows selected. SQL> SQL> SQL> select first_name,last_name,salary from hr.employees where department_id in (20,30,40) and EXISTS ( select department_id from hr.employees where department_id=100000); no rows selected SQL> SQL> select first_name,last_name,salary from hr.employees where department_id in (20,30,40) and NOT EXISTS ( select department_id from hr.employees where department_id=100000); FIRST_NAME LAST_NAME SALARY -------------------- ------------------------- ---------- Michael Hartstein 14000 Pat Fay 7000 Den Raphaely 12000 Alexander Khoo 4100 Shelli Baida 3900 Sigal Tobias 3800 Guy Himuro 3600 Karen Colmenares 3500 Susan Mavris 7500 9 rows selected. SQL>
SQL> select count(department_id),salary from hr.employees group by department_id,salary having sum(salary)>10000 and EXISTS ( select department_id from hr.employees where department_id=10);
COUNT(DEPARTMENT_ID) SALARY
-------------------- ----------
3 3600
1 15000
3 10500
2 9000
1 12500
2 23000
3 4100
2 10000
2 8500
1 10600
3 3800
COUNT(DEPARTMENT_ID) SALARY
-------------------- ----------
1 11000
1 13008
5 3500
2 12000
1 13008
1 12000
4 4200
2 7200
1 14000
1 14500
1 13000
COUNT(DEPARTMENT_ID) SALARY
-------------------- ----------
2 11500
3 11000
2 5800
2 8000
26 rows selected.
SQL>
Do you want to learn Oracle SQL Tutorial for Beginners, then read the following articles.
Oracle SQL Tutorials For Beginners – Learn Oracle SQL from scratch with Oracle SQL Online Course