SELECT * FROM EMPLOYEES
WHERE DEPARTMENT_ID=30;
In the above query, the department ID value has been provided, and is used on the right hand side of the WHERE condition. However, such constant values might not also be provided or known. For example, consider the query re-phrased as - retrieve the details of employees who belong to the same department as 'Alexander Khoo'. Here the department number has not been provided. Instead the name of an employee is given. Using this name, you would need to first find out - to which department does Alexander Khoo belong. Let say this is some value 'X'. You would have to proceed further to find out all the other employees who belong to the department X.
If you notice this is a 2-step process involving:
1) Which department does Alexander Khoo belong to.
2) Who are the others who belong to the department number returned by the first step.
Subquery Syntax:
SELECT select_list
FROM table_name
WHERE column_name operator (SELECT select_list
FROM table_name
…)