SQL - The Shortest Route For Beginners (2015)

Chapter 5. Subqueries

A query is an operation that retrieves data from one or more tables or views. In the current context, a top-level SELECT statement is called main query, and a query nested within a clause of the main query is called a subquery, as illustrated in the following figure. Subqueries are primarily used in situations where the criteria for the data being queried are unknown.

Guidelines :

· A SELECT statement embedded in a clause of another SQL statement is called a subquery.

· A subquery can be placed in the following SQL command clauses: WHERE, HAVING, INTO ( a clause of INSERT statement), SET (a clause of UPDATE statement), FROM (a clause used in SELECT and DELETE statements).

· Subqueries must be enclosed in parentheses.

· Subqueries are defined to the right side of the operator.

· Use single row comparison operator (=,>,>=,<,<=,<>) in subqueries that return a single row. For subqueries that return multiple rows, use multi row operators (IN and NOT IN).

· It executes once before the main query, and its result is used to complete the condition defined in the main or outer query.

· Subqueries must not contain the ORDER BY clause, which, if required, should be used in the main SELECT statement.

· You can place any number of subqueries in a single SELECT statement, nested to any level; however, performance must be taken care of while using deep nesting.

Understanding Subquery Process

You will go through a simple example to understand how a subquery, nested in a SELECT statement, is processed. Suppose you want to retrieve names of employees and their respective job ids who are working in the same department as Adam. Keeping in view the existing skills, that you have achieved so far, you will simply pass two separate statements to get the desired result. The first statement would be something like this:

SELECT department_id FROM employees WHERE first_name=’Adam’;

This statement would return the department number of Adam i.e. 50. Now, to find out other employees working in this department, you would pass the second statement, as follows, to get the required output:

SELECT first_name, job_id FROM employees WHERE department_id=50;

What would you do if you’re asked to fetch this result using a single statement, rather than two. The answer is subquery. With the help of a subquery, you can combine the above two statements into one to produce the same result. Here is how it would be done:

In this statement the first query (a subquery) nests into the second one, and is executed first, producing the result: 50. The second query (the main query) is then processed and uses the value (50), returned by the subquery, to complete its WHERE clause.

Note that the subquery used in this example is called a single row subquery, because it returned just one row. In such subqueries, you can only use single row comparison operators.

SQL Statement :

SELECT first_name, job_id, department_id

FROM employees

WHERE department_id =

(SELECT department_id

FROM employees

WHERE first_name=’Adam’)

ORDER BY first_name;

Output :

Handling Multiple Row Subquery

Subqueries are used to find the data with unknown values, just like the one you used in the previous example to find the department number of Adam together with other information about his colleagues, based on the department number. In that example the subquery returned a single value (the department number of Adam) to process and return the required information. Run the same query for Jennifer. This time you will get an error, saying: single-row subquery returns more than one row. The subquery failed because it found two employees with the same first name: Jennifer Dilly in department number 50, and Jennifer Whalen in department number 10. There are two ways to avoid this error. Number one, modify the WHERE clause like this: WHERE first_name=’Jennifer’ AND last_name=’Dilly’ (if you wish to see the output for department number 50). Number two, use a multiple row comparison operator, such as IN, as demonstrated below. The modified WHERE clause – mentioned in the first recommendation above – would only fetch information of employees working under department 50, while the multiple row comparison operator would also return the sole record of Jennifer Whalen enrolled in department number 10. See record number 16 in the output screenshot.

In this case the subquery returned two departments (10 and 50). In order to handle these multiple values, we used the IN operator, which accepts a list of values. In turn, the main query returned all records for department number 50 (which Jennifer Dilly belongs to), and the sole record of Jeniffer Whalen, who works in department number 10.

SQL Statement :

SELECT first_name||' '||last_name, job_id, department_id

FROM employees

WHERE department_id IN (SELECT department_id

FROM employees

WHERE first_name=’Jennifer’)

ORDER BY first_name;

Output :

Using Multiple Subqueries in a SELECT Statement

You can define multiple subqueries within a nested query by joining the specified conditions using the AND and OR logical operators. For example, suppose you want to retrieve the names of all employees who have the same job id value as Jennifer Dilly and work in the same department with her. The statement will use two subqueries, joined together using the AND operator, and will be structured as follows. Note that in this statement we used the single row comparison operator (=) to evaluate the job id of Jennifer Dilly, because in the second subquery we eliminated return of multiple rows by specifically mentioning which Jennifer we are referring to. Jennifer Dilly works as shipping clerk (SH_CLERK), so the output will only display her colleagues working as shipping clerk, and will suppress records of two other designations (ST_CLERK – Stock Clerk and ST_MAN – Stock Manager) from her department.

SQL Statement :

SELECT first_name||' '||last_name, job_id, department_id

FROM employees

WHERE department_id IN (SELECT department_id

FROM employees

WHERE first_name='Jennifer')

AND job_id = (SELECT job_id

FROM employees

WHERE first_name='Jennifer' AND last_name='Dilly')

ORDER BY first_name;

Output :

Subquery in the Having Clause

You added subqueries to the WHERE clause in the former two examples. Subqueries can also be used in the HAVING clause. The first query below provides a list of average salaries in each department. It is an auxiliary statement provided to comprehend the result of the second query, which displays all the departments that have an average salary greater than that of department number 80. It is the query which uses a subquery in its HAVING clause. From the first output, you can see that the average salary for department number 80 is 8956. This is the figure which is passed on to the main query to complete the HAVING condition. Consequently, the second output displays the result you intend to see.

SQL Statement :	SQL Statement :
SELECT department_id, round(avg(salary)) FROM employees GROUP BY department_id ORDER BY department_id;	SELECT department_id, round(avg(salary)) FROM employees GROUP BY department_id HAVING avg(salary) > (SELECT avg(salary) FROM employees WHERE department_id=80) ORDER BY department_id;
Output :	Output :