Oracle Tutorials Structured Query Language SQL Subqueries
| 
| 
Video Tutorials on Subqueries
|
Theory on Subqueries
|
Subqueries (Hands-On)
|
A subquery is a type of SQL query, where a query is embedded within another query. Sub-queries are very powerful. To help you understand a subquery consider the following SELECT statement to retrieve the details of employees who belong to department 30.
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
…)
In the syntax, observe a second SELECT statement written in the WHERE clause, on the right hand side of the WHERE condition. This SELECT statement is enclosed in parantheses. This subquery is called the inner query and is executed once to return a value that is used by the main (outer) query. Subqueries can be different in different places in a SELECT statement, such as the WHERE clause, HAVING clause, FROM clause, SELECT column list etc.
The query to retrieve the details of employees who belong to the same department as Alexander Khoo is :
SELECT *
FROM EMPLOYEES
WHERE DEPARTMENT_ID = (SELECT DEPARTMENT_ID
FROM EMPLOYEES
WHERE FIRST_NAME='Alexander' AND LAST_NAME='Khoo')
Some guidelines related to subqueries are:
- Enclose subqueries in parentheses.
- Place subqueries on the right side of the comparison condition.
- Use single-row operators with single-row subqueries.
- Use multiple-row operators with multiple-row subqueries.
A single-row subquery is one where the subquery returns only one value. In such a subquery you must use a single-row operator such as:
| Operator | Description |
| = | Equal To |
| <> | Not Equal To |
| > | Greater Than |
| >= | Greater Than Equal To |
| < | Less Than |
| <= | Less Than Equal To |
The single-row operators are used to write single-row subqueries. The table below demonstrates the use of the single-row operators in writing single-row subqueries.
| Operator | Query | Example |
|
= |
Retreive the details of employees who get the same salary as the employee whose ID is 101. |
|
|
<> |
Retreive the details of departments that are not located in the same location ID as department 10. | SELECT * |
|
> |
Retrieve the details of employees whose salary is greater than the minimum salary. | SELECT * |
| >= | Retrieve the details of employees who were hired on or after the same date that employee 201 was hired. |
SELECT * FROM EMPLOYEES YEES WHERE EMPLOYEE_ID=201); |
| < | Retrieve the details of employees whose salary is less than the maximum salary of employees in department 20. | SELECT * FROM EMPLOYEES DEPARTMENT_ID=20); |
| <= | Retrieve the details of employees who were hired on or before the same date that employee 201 was hired. | SELECT * FROM EMPLOYEESWHERE HIRE_DATE <=(SELECT EMPLOYEE_ID=201); |
A multiple row subquery is one where the subquery may return more than one value. In such type of subquery, it is necessary to use a multiple-row operator. If not you might get the ORA-01427 error: single-row subquery returns more than requested number of rows.
The table below describes the multiple-row operators that can be used when writing multiple-row subqueries:
| Operator | Meaning |
| IN | Equal to any value returned by the subquery |
| ANY | Compare value to each value returned by the subquery |
| ALL | Compare value to every value returned by the subquery |
The multiple-row operators are used to write multiple-row subqueries. The table below demonstrates the use of the multiple-row operators in writing multiple-row subqueries.
| Operator | Query | Example |
| IN | Retreive the department ID, department name and location ID of departments that are located in the same location ID as a location in the UK. | |
|
>ALL (Greater than the maximum returned by the subquery) |
Retrieve the first name of employees whose salary is greater than the all the salaries of employees belonging to department 20. |
SELECT FIRST_NAME |
|
<ALL (Less than the least value returned by the subquery) |
Retrieve the first name of employees whose salary is less than all the salaries of employees belonging to department 20. |
SELECT FIRST_NAME |
|
>ANY (Greater than the minimum value returned by the subquery) |
Retrieve the first name of employees whose salary is greater than the minimum salary of employees in department 60. |
SELECT FIRST_NAMEWHERE SALARY > ANY (SELECT SALARY FROM EMPLOYEES WHERE DEPARTMENT_ID=60) |
|
<ANY (Less than the maximum value returned by the subquery) |
Retrieve the first name of employees whose salary is less than the maximum salary of employees in department 60. |
SELECT FIRST_NAME |
In this article you learned about subqueries and the different kinds of subqueries available in Oracle.
Try the hands-on exercise on subqueries tables in the database, using our Sample Tables
