Single-Row Subqueries in SQL
Objectives
This tutorial covers the following objectives:
- Construct and execute a single-row subquery in the WHERE clause or HAVING clause
- Construct and execute a SELECT statement using more than one subquery
- Construct and execute a SELECT statement using a group function in the subquery
Purpose
- As you have probably realized, subqueries are a lot like Internet search engines.
- They are great at locating the information needed to accomplish another task.
- In this lesson, you will learn how to create even more complicated tasks for subqueries to do for you.
- Keep in mind that subqueries save time in that you can accomplish two tasks in one statement.
Facts About Single-row Subqueries
- They:
- Return only one row
- Use single-row comparison operators (=, >,>=, <, <=, <>)
- Always:
- Enclose the subquery in parentheses.
- Place the subquery on the right hand side of the comparison condition.
Additional Subquery Facts
- The outer and inner queries can get data from different tables.
- Only one ORDER BY clause can be used for a SELECT statement, and if specified, it must be the last clause in the main SELECT statement.
- The only limit on the number of subqueries is the buffer size that the query uses.
Subqueries from Different Tables
- The outer and inner queries can get data from different tables.
- Who works in the Marketing department?
SELECT last_name, job_id, department_id
FROM employees
WHERE department_id =
(SELECT department_id
FROM departments
WHERE department_name = 'Marketing')
ORDER BY job_id;


The sub-query finds the department_id for ‘Marketing’, the outer query uses the returned department_id to display rows from the employees table.
- More than one subquery can return information to the outer query.
SELECT last_name, job_id, salary, department_id
FROM employees
WHERE job_id =
(SELECT job_id
FROM employees
WHERE employee_id = 141)
AND department_id =
(SELECT department_id
FROM departments
WHERE location_id = 1500);


- The first subquery returns the job_id of employee 141 (ST_CLERK).
- The second subquery uses the departments table to find the department_id at location_id 1500 (50).
- The outer query then returns rows from the employees table that match both these values.
Group Functions in Subqueries
- Group functions can be used in subqueries.
- A group function without a GROUP BY clause in the subquery returns a single row.
- The query on the next slide answers the question, “Which employees earn less than the average salary?”
- The subquery first finds the average salary for all employees, the outer query then returns employees with a salary of less than the average.
SELECT last_name, salary
FROM employees
WHERE salary <
(SELECT AVG(salary)
FROM employees);


Subqueries in the HAVING Clause
- Subqueries can also be placed in the HAVING clause.
- Remember that the HAVING clause is similar to the WHERE clause, except that the HAVING clause is used to restrict groups and always includes a group function such as MIN, MAX, or AVG.
- Because the HAVING clause always includes a group function, the subquery will nearly always include a group function as well.
Subquery Example
- Which departments have a lowest salary that is greater than the lowest salary in department 50?
- In this example, the subquery selects and returns the lowest salary in department 50.
SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) >
(SELECT MIN(salary)
FROM employees
WHERE department_id = 50);


- The outer query uses this value to select the department ID and lowest salaries of all the departments whose lowest salary is greater than that number.
- The HAVING clause eliminated those departments whose MIN salary was less than department 50’s MIN salary.
SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) >
(SELECT MIN(salary)
FROM employees
WHERE department_id = 50);


Summary
In this lesson, you should have learned how to:
- Construct and execute a single-row subquery in the WHERE clause or HAVING clause
- Construct and execute a SELECT statement using more than one subquery
- Construct and execute a SELECT statement using a group function in the subquery
0 Comments