Subquery: Inner Queries
A second SELECT statement ( Subquery (subquery)) can be included in a SELECT statement. A value or set of values is generated by the Subquery as part of the main statement.
You can use the demo data for the SQL tutorial. Start the Database Studio as database administrator MONA with the password RED and log on to demo database DEMODB: Logging On to a Database.
You can use Database Studio to enter and execute SQL statements. More information: Working with SQL Statements: Overview
Subquery
SELECT name, zip
FROM hotel.customer
WHERE zip = (SELECT MAX(zip) FROM hotel.customer)
Displaying the customer with the greatest zip code
Result
|
NAME |
ZIP |
|
Doe |
97213 |
See also: SQL Reference Manual, Comparison Predicate (comparison_predicate)
SELECT hno, name, zip
FROM hotel.hotel
WHERE zip IN(SELECT zip FROM hotel.customer WHERE title = 'Mrs')
Displaying the hotels located in cities/places in which female customers also live
Result
|
HNO |
NAME |
ZIP |
|
10 |
Congress |
20005 |
See also: SQL Reference Manual, IN Predicate (in_predicate)
SELECT hno, name
FROM hotel.hotel
WHERE name = ANY(SELECT name FROM hotel.city)
Displaying hotels that have the same name as other cities in the city table. The inner query determines the list of city names with which the hotel names are compared.
Result
|
HNO |
NAME |
|
20 |
Long Island |
|
120 |
Long Beach |
SELECT cno, name
FROM hotel.customer
WHERE cno = ANY(SELECT cno FROM hotel.reservation WHERE arrival > '2005-01-01')
Displaying the customers that have made all reservations as of a specified date
Result
|
CNO |
NAME |
|
3600 |
Smith |
|
3900 |
Howe |
Use ANY if the Subquery returns more than one value and you want to take this into consideration in the WHERE clause condition that usually requires exactly one value.
See also: SQL Reference Manual, Quantified Predicate (quantified_predicate)
Correlated Subquery
You can use Correlated Subqueries to formulate conditions for selecting rows; these conditions are to apply to groups of rows only and not to all rows in a table.
Subqueries are only evaluated once. Correlated Subqueries are evaluated for each row in the external table, in the case of compound Subqueries from the inside out.
SELECT hno, type, price
FROM hotel.room room_table
WHERE price =
(SELECT MAX(price) FROM hotel.room WHERE type = room_table.type)
Displaying the hotels (in the form of the hotel number) whose rooms in the individual types (single, double, or suite) have the highest price
Since in this example, the same table is addressed in the external SELECT statement as in the Subquery, you have to specify a reference name ( room_table). A row from the result of the external SELECT statement is linked (correlated) to a value in the Subquery.
Explanation
-
SELECT hno, type, price FROM hotel.room room_table Searches for the hotel number, room type and price in the room table and renames the table room_table.
-
WHERE price= Contains the row in which the price is the same as the result of the following Subquery:
-
(SELECT MAX(price) FROM hotel.room (Start the Subquery) Searches for the maximum price in the room table
-
WHERE type = room_table.type) The room types here are to be the same as the room types in the rows found above
Result
|
HNO |
TYPE |
PRICE |
|
130 |
double |
270 |
|
130 |
single |
160 |
|
130 |
suite |
700 |
SELECT cno, title, name
FROM hotel.customer
WHERE EXISTS
(SELECT * FROM hotel.reservation
WHERE hotel.customer.cno = hotel.reservation.cno)
Displaying only those customers from the customer table for which one or more reservations exist
Result
|
CNO |
TITLE |
NAME |
|
3000 |
Mrs |
Porter |
|
3100 |
Mr |
Brown |
|
3200 |
Company |
Datasoft |
|
3600 |
Mrs |
Smith |
|
3900 |
Mr |
Howe |
|
4100 |
Mrs |
Baker |
|
4300 |
Company |
TOOLware |
|
4400 |
Mr |
Jenkins |
Use EXISTS if you only want the Subquery to find out whether a row exists that fulfils a specific condition.
See also: SQL Reference Manual, EXISTS Predicate (exists_predicate)
More examples for Data Query