Subquries.pdf

Databases

Computer Laboratory

Subqueries

Dariusz Jankowski©

1 / 8

1. Using a Subquery to Solve a Problem

Subquery Syntax

Using a Subquery

Guidelines for Using Subqueries

2 / 8

Types of Subqueries

A SELECT statement can be considered as a query block. The example displays

employees whose job ID is the same as that of employee 141 and whose salary is greater than

that of employee 143.

3 / 8

The example consists of three query blocks: the outer query and two inner queries.

The inner query blocks are executed first, producing the query results ST_CLERK and 2600,

respectively. The outer query block is then processed and uses the values that were returned

by the inner queries to complete its search conditions.

Both inner queries return single values (ST_CLERK and 2600, respectively), so this

SQL statement is called a single-row subquery.

Note: The outer and inner queries can get data from different tables.

You can display data from a main query by using a group function in a subquery to

return a single row. The subquery is in parentheses and is placed after the comparison

condition.

The example displays the employee last name, job ID, and salary of all employees

whose salary is equal to the minimum salary. The MIN group function returns a single value

(2500) to the outer query.

One common error with subqueries occurs when more than one row is returned for a single-

row subquery.

In the SQL statement in the slide, the subquery contains a GROUP BY clause, which implies

that the subquery will return multiple rows, one for each group that it finds. In this case, the

result of the subquery are 4400, 6000, 2500, 4200, 7000, 17000, and 8300.

The outer query takes those results and uses them in its WHERE clause. The WHERE clause

contains an equal (=) operator, a single-row comparison operator that expects only one value.

The = operator cannot accept more than one value from the subquery and therefore generates

the error.

4 / 8

To correct this error, change the = operator to IN.

A common problem with subqueries occurs when no rows are returned by the inner query.

In the SQL statement in the slide, the subquery contains a WHERE clause. Presumably, the

intention is to find the employee whose name is Haas. The statement is correct but selects no

rows when executed.

There is no employee named Haas. 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 ID equal to null, and so returns no rows. If a job existed with a

value of null, the row is not returned because comparison of two null values yields a null;

therefore, the WHERE condition is not true.

The ANY operator (and its synonym, the SOME operator) compares a value to each value

returned by a subquery. The slide example displays employees who are not IT programmers

and whose salary is less than that of any IT programmer. The maximum salary that a

programmer earns is $9,000.

< ANY means less than the maximum.

> ANY means more than the minimum.

5 / 8

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika: