Subqueries

A query within a query is referred to as a subquery. A subquery responds to questions with multiple parts; the subquery responds to one part of the question while the parent query responds to the other. You may be wondering where we can use these subqueries. If you’re wondering, congratulations ? .

We’ve so far seen how to use the SELECT clause, single-row functions and group functions. Sometimes we may not be able to get the kind of data we need using only the above unless we use a mixture of them. For example, assume we need to know those employees whose salary is above the average salary or those who work in the same department as Sarah. In such cases, we do use subqueries. Subqueries work with the condition of the WHERE clause.

SELECT columns FROM table WHERE condition (subquery);

From the above syntax, the subquery is worked on first and the result is considered to be the condition to work on the first query (outer or parent query).

Lets use an example. We need to get employees who earn above the average salary. To do that, lets first get the average salary.

SELECT AVG(salary) FROM employees;

Yet to get detail details, let use the following query.

SELECT first_name, last_name, phone_number, salary FROM employees;

Now to get our answer done, let us use the two queries.

SELECT first_name, last_name, phone_number, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

We can get a few more examples to understand the subqueries.

Lets get an employee who earns the highest salary.

To get this, we need to know what the highest salary is.

SELECT MAX(salary) FROM employees;

Then let us write a query to get employee details.

SELECT first_name, last_name, job_id FROM employees;

Then let us join the two queries to get the desired output.

SELECT first_name, last_name, job_id, salary FROM employees WHERE salary = (SELECT MAX(salary) FROM employees);

Let us get employees who get the same salary as the employee whose ID is 163.

TO work on this, we need to know the salary of employee 163. Then, we get those employees who earn the salary as his or hers. To get that, lets use this query.

SELECT salary FROM employees WHERE employee_id = 163;

Then, let us get the employee details.

SELECT first_name, last_name, phone_number, salary FROM employees;

Let us write the subquery using the above two queries.

SELECT first_name, last_name, phone_number, salary FROM employees WHERE salary = (SELECT salary FROM employees WHERE employee_id = 163);

Lets get employee details (employee_id, name, salary, department id, job id) for those employees who work in the same designation as the employee whose employee id is 169.

To get this done, we need to know the designation of employee 169. We then use that as a condition to get the first query (all employees).

SELECT employee_id, first_name, last_name, department_id, job_id FROM employees WHERE job_id = (SELECT job_id FROM employees WHERE employee_id = 169);

Let us wind up with this example. Lets get all employees who report to the same manager as Payam.

SELECT employee_id, first_name, last_name, manager_id, salary FROM employees WHERE manager_id IN (SELECT employee_id FROM employees WHERE first_name = 'Payam');

Summary

1. Subquery is a query within another query.

2. The inner query is worked on first and the result is used to get the results of the outer query.