Lesson 45 : Subquery

Subquery 

Subquery  is a query whithin  in a query. It is also called as Inner query or Nested query. A subquery is usually added in the WHERE clause of the sql statement.  Usually, a subquery is used when we know how to search for a value using a SELECT statement, but do not know the exact value.

Here are few points to remember,

· Must be enclosed in parentheses

· Can be the object of an IN or NOT IN clause

· Can be the object of EXISTS or NOT EXISTS clause

· Support quantifiers ALL, ANY, SOME

· Support LIKE or NOT LIKE used with a quantifier

· Can specify more than one column to match

· Generate a DISTINCT list of values

Example :

To list out employees who are working in Boston.

SQL> SELECT * from emp WHERE deptno IN (SELECT deptno from dept where location='BOSTON');


Example :

To list out the department managers whose salaries are less than 35,000 and whose budget amounts are greater than 7500000.

SQL> SELECT last_name,first_name FROM employee WHERE salary_amount<35000 AND (SELECT manager_employee_number FROM department WHERE budget_amount >7500000 ) ;