Database Systems II

Subqueries in MySQL

Dr. Shamal AL-Dohuki

[email protected]
Objectives

• After completing this class, you should be able to do the

following:
• Describe the types of problems that subqueries can solve

• Define subqueries
• List the types of subqueries
• Write single-row and multiple-row subqueries
Using a Subquery to Solve a Problem
• Who has a salary greater than Azad?
Main Query

“Which employees have a salary greater

?
than Azad’s salary?”

Subquery

“What is Honar’s salary?”

Subqueries
• A subquery is a SELECT statement that is embedded in a clause of another SELECT
statement.
• You can build powerful statements out of simple ones by using subqueries.
• They can be very useful when you need to select rows from a table with a condition
that depends on the data in the table itself.
• You can place the subquery in a number of SQL clauses:
• WHERE clause, HAVING clause, or FROM clause

• In the syntax: includes a comparison operator such as >, =, or IN operator

Subqueries (Cont.)
• Subqueries are commonly used in:
• WHERE clauses (filtering based on subquery results).
• FROM clauses (treating subquery results as temporary tables).
• SELECT clauses (returning computed values).
• HAVING clauses (filtering grouped data).
Subqueries
• The subquery (inner query) executes once before the main query.

• The result of the subquery is used by the main query (outer query).

SELECT select_list
FROM table
WHERE expr operator
(SELECT select_list
FROM table);
Using a Subquery
• In the following example, the inner query determines the salary of employee 1010.
• The outer query takes the result of the inner query and uses this result to display all
the employees who earn more than this amount.
SQL> SELECT ename
2 FROM emp 4500
3 WHERE sal >
4 (SELECT sal
5 FROM emp
6 WHERE empno=1010);

ENAME
----------
Jwan
Ziyad
Lava
Guidelines for Using Subqueries
• Enclose subqueries in parentheses.

• Place subqueries on the right side of the comparison operator.

• Do not add an ORDER BY clause to a subquery.

• Use single-row operators with single-row subqueries.

• Use multiple-row operators with multiple-row subqueries.

Types of Subqueries
• Single-row subquery : Queries that return only one row from the inner SELECT
statement.

• Multiple-row subquery: Queries that return more than one row from the inner
SELECT statement.

• Multiple-column subquery: Queries that return more than one column from the
inner SELECT statement.
Types of Subqueries
Main query
• Single-row subquery returns
Subquery Assistant Professor

Main query
• Multiple-row subquery returns Assistant Professor
Subquery
Lecturer

Main query
• Multiple-column subquery returns Assistant Professor 4500
Subquery
Lecturer 2000
Single-Row Subqueries
• Return only one row

• Use single-row comparison operators Operator Meaning

= Equal to
• Example: Display the employees whose
> Greater than
job title is the same as that of employee >= Greater than or equal to

1009. < Less than

<= Less than or equal to

<> Not equal to

Single-Row Subqueries (Example)
SQL> SELECT ename, job
2 FROM emp
3 WHERE job =
4 (SELECT job
5 FROM emp
6 WHERE empno = 1009);

ENAME JOB
---------- ---------
Azad Chair
Newroz Chair
Haji Chair
…….
Single-Row Subqueries (Example 2)
• The example in the next slide displays employees whose job title is the same as
that of employee 1009 and whose salary is greater than that of employee 1009.
Single-Row Subqueries (Example 2)
SQL> SELECT ename, job
2 FROM emp
3 WHERE job = Chair
4 (SELECT job
5 FROM emp
6 WHERE empno = 1009)
7 AND sal > 4500
8 (SELECT sal
9 FROM emp
10 WHERE empno = 1009);

ENAME JOB
---------- ---------
Azad Chair
Newroz Chair
Haji Chair
Using Group Functions in a Subquery
• Display the employee name, job title, and salary of all employees whose salary is
equal to the minimum salary.

SQL> SELECT ename, sal

2 FROM emp
3 WHERE sal =
4 (SELECT MIN(sal)
5 FROM emp);

ENAME
----------
Alend 3500
Adam 3500
HAVING Clause with Subqueries
• This query displays all the departments that have a minimum salary greater than that
of department 3 (GE).

SQL> SELECT deptno, MIN(sal)

2 FROM emp
3 GROUP BY deptno 3500
4 HAVING MIN(sal) >
5 (SELECT MIN(sal)
6 FROM emp
7 WHERE deptno = 3);
What Is Wrong with This Statement?
SQL> SELECT empno, ename
2 FROM emp
3 WHERE sal =
4 (SELECT MIN(sal)
5 FROM emp
6 GROUP BY deptno);

ERROR:
single-row subquery returns more than one row

no rows selected
Errors with Subqueries
• One common error with subqueries is more than one row returned for a single-row subquery.
• In the SQL statement on the previous slide, the subquery contains a GROUP BY (deptno) clause, which
implies that the subquery will return multiple rows, one for each group it finds.
• The outer query takes the results of the subquery (multiple rows) and uses these results in its WHERE
clause.
• The WHERE clause contains an equal (=) operator, a single-row comparison operator expecting only
one value.
• The = operator cannot accept more than one value from the subquery and hence generates the error.
• To correct this error, change the = operator to IN.
IN Clause with Subqueries
• Find employees working in departments that have a "Chair" role.

SQL> SELECT ename, deptname

2 FROM emp
3 WHERE deptno IN
4 SELECT deptno
5 FROM. emp
6 WHERE job = ‘Chair’);
Will This Statement Work?
SQL> SELECT ename, job
2 FROM emp
3 WHERE job =
4 (SELECT job
5 FROM emp
6 WHERE ename='Jihan');

no rows selected
Problems with Subqueries
• A common problem with subqueries is no rows being returned by the inner query.
• In the SQL statement on the slide, the subquery contains a WHERE (ename='Jihan') clause.
• The intention is to find the employee whose name is Jihan.
• The statement seems to be correct but selects no rows when executed.
• The problem is that Jihan is not inserted or misspelled.
• There is no employee named Jihan, So the subquery returns no rows.
• The outer query takes the results of the subquery (null) and uses these results in its WHERE
clause.
• The outer query finds no employee with a job title equal to null and so returns no rows.
Exercises
• Write a query to display the employee name, job, and department name for all employees
in the same department as Lina.

• Write a query to display the employee name, job, and department name for all employees

in the same department as Lina. Exclude Lina.

• Write a query to display the employee number and name for all employees who earn

more than the average salary. Sort the results in descending order of salary.
Multiple-Row Subqueries
• Return more than one row

• Use multiple-row comparison operators

Operator Meaning

IN Equal to any member in the list

ANY Compare value to each value returned by

the subquery

Compare value to every value returned by

ALL
the subquery
ANY & ALL
• In MySQL, ANY and ALL are used in combination with comparison operators to

compare a value to a set of values returned by a subquery.

• These operators are typically used with subqueries and are useful when you want to

compare a single value to multiple values within a subquery result.

• ANY returns true if the comparison is true for at least one value in the set.

• ALL returns true if the comparison is true for all values in the set.
Using ANY Operator in Multiple-Row Subqueries
• Display the employees whose salary is less than any Associate Professor and not Associate Professor.

SQL> SELECT ename, job, sal 3500

2 FROM emp 3000
3 WHERE sal < ANY
4 (SELECT sal
5 FROM emp
6 WHERE job = 'Associate Professor')
7 AND job <> 'Associate Professor';

ENAME Job Sal

--------- -------------------- ---------
Haval Assistan Professor 2000
Shilan Lecturer 1500
. . . . .
Using ALL Operator in Multiple-Row Subqueries
• Display the employees whose salary is greater than the average salaries of all the departments.

SQL> SELECT ename, job, sal

2 FROM emp
3 WHERE sal > ALL
4 (SELECT avg(sal)
5 FROM emp
6 GROUP BY deptno);
Multiple-Column Subquery
• You can write subqueries that return multiple columns.

SQL> SELECT ename, job, sal

2 FROM emp
3 WHERE (job, sal) IN
4 (SELECT job, avg(sal)
5 FROM emp
6 GROUP BY job);