PL/SQL Exists

The EXISTS operator is used to correlate rows from an SELECT statement with the other one being an subquery.

Course table

COURSE_ID NAME DESCRIPTION DURATION PRICE
1 SQL 1 SQL course for beginners 1 week 10
2 SQL 2 SQL course for advanced 2 week 50
3 HTML5 Learn HTML 5 1 week 10
4 PHP PHP course 4 week 75
5 CSS Learn CSS 2 week 20

Orders table

ORDER_ID COURSE_ID STUDENT_ID ORDER_DATE
1 1 6 07-AUG-2014 10:11:03
2 5 7 23-SEP-2014 12:31:41
3 4 5 29-AUG-2014 09:55:54
21 5 6 01-DEC-2014 16:32:36
32 1 4 17-DEC-2014 17:22:27

Exists example

 
SELECT * 
FROM course c 
WHERE EXISTS (select * from orders o where o.course_id=c.course_id);

Output

COURSE_ID NAME DESCRIPTION DURATION PRICE
1 SQL 1 SQL course for beginners 1 week 10
5 CSS Learn CSS 2 week 20
4 PHP PHP course 4 week 75

Not Exists example

 
SELECT * 
FROM course c 
WHERE NOT EXISTS (select * from orders o where o.course_id=c.course_id);

Output

COURSE_ID NAME DESCRIPTION DURATION PRICE
3 4 5 29-AUG-2014 09:55:54
2 5 7 23-SEP-2014 12:31:41