EXPLAIN Statement for SELECT Statements with Subqueries 

To determine the cost of search strategies where the value of a column is compared with the hit rows of a subquery, the SQL Optimizer must know the number of hit rows. However, the number of hit rows is not known until the subquery has been processed.

For a SELECT statement with subqueries, the EXPLAIN statement determines the possible search strategies, but does not execute these subqueries. Therefore, the search strategy NO STRATEGY NOW (ONLY AT EXECUTION TIME) is usually displayed for the outer SQL statement.

Examples

The examples used to explain the SQL Optimizer are based on the demo database DEMODB and its complete demo data in the schema HOTEL.

Concepts of the Database System, Objects in the Schema HOTEL

EXPLAIN
SELECT name FROM hotel.city
  WHERE name IN (SELECT name FROM hotel.hotel)

Qualification: name IN (SELECT name FROM hotel.hotel)
Primary key of the table CITY: zip
Primary key of the table HOTEL: cno
Indexes of qualified columns: none
Search strategy used, outer SELECT: TABLE SCAN
Search strategy used, subquery: TABLE SCAN

Result of the EXPLAIN Statement

TABLENAME	COLUMN_OR_INDEX	STRATEGY	PAGECOUNT
HOTEL		TABLE SCAN	8
CITY		TABLE SCAN	11
		RESULT IS COPIED COSTVALUE IS	101

All of the city names are selected after which a hotel is named. As no suitable index exists for the two tables and there is no restriction to the primary key columns, both SELECT statements are processed with a TABLE SCAN.

EXPLAIN
SELECT cno, name FROM hotel.customer
  WHERE cno = ANY(SELECT cno FROM hotel.reservation WHERE arrival > '2005-01-01')

Qualification: cno = ANY(SELECT cno FROM hotel.reservation WHERE arrival > '2005-01-01')
Primary key for table CUSTOMER: cno
Primary key for table RESERVATION: rno
Indexes of qualified columns: none
Search strategy used, outer SELECT: NO STRATEGY NOW (ONLY AT EXECUTION TIME)
Search strategy used, subquery: TABLE SCAN

Result of the EXPLAIN Statement

TABLENAME	COLUMN_OR_INDEX	STRATEGY	PAGECOUNT
RESERVATION		TABLE SCAN	24
CUSTOMER		NO STRATEGY NOW (ONLY AT EXECUTION TIME)
		RESULT IS COPIED COSTVALUE IS

All the customers are displayed that have made reservations as of a specified date. A TABLE SCAN is performed for the table RESERVATION. For the outer SELECT statement, the search strategy NO STRATEGY NOW (ONLY AT EXECUTION TIME) is displayed.

You will find additional simple example SQL statements in the SQL Tutorial, Subquery: Inner Queries.

See also:

SUBQ CONDITION FOR KEY

SUBQ CONDITION FOR INDEX

List of All Search Strategies

Reference Manual, Subquery (subquery)