SQL: Structured Query Language - Oracle 10g Database Administration Concepts and Implementation Made Simple - page 19

SELECT *
FROM emp
WHERE sal IN (SELECT sal
FROM emp
WHERE deptno = 30);
By having NOT before IN can completely invert the result like
in the following example. Such types of queries fall under the
category called “Sub-Queries” which we will discuss in the
article ahead in this chapter. There is a special technique to
interpret them.
SELECT *
FROM emp
WHERE sal NOT IN (SELECT sal
FROM emp
WHERE deptno = 30);
Example:
SELECT *
FROM emp
WHERE sal BETWEEN 2000 AND 3000;
Only those records will be displayed where the salary is between
2000 and 3000 including both 2000 and 3000.
Example:
SELECT ename, deptno
FROM dept
WHERE EXISTS (SELECT *
FROM emp
WHERE dept.deptno = emp.deptno);
TRUE if a sub-query returns at least one row. In other words the
output will be two columns from dept table and all the records,
only if the query after EXISTS results in at least one record.