Database tip: All about subqueries

Subqueries

Subqueries, also called inner queries, appear within a WHERE or HAVING clause of another SQL statement or in the SELECT list of a statement. You can use subqueries to handle query requests that are expressed as the results of other queries. A statement that includes a subquery operates on rows from one table, based on its evaluation of the subquery's select list, which can refer to the same table as the outer query, or to a different table. In T-SQL, a subquery can also be used almost anywhere an expression is allowed, if the subquery returns a single value. A case expression can also include a subquery.

SELECT statements that contain one or more subqueries are sometimes called nested queries or nested SELECT statements. The practice of nesting one SELECT statement inside another is one reason for the word "structured" in "Structured Query Language."

Many SQL statements that include a subquery as joins can be formulated as joins. Other statements can be expressed only using subqueries. Some people prefer subqueries to alternative formulations, because they find subqueries easier to understand. Other SQL users avoid subqueries whenever possible. You can choose whichever formulation you prefer. (ASE converts some subqueries into joins before processing them.)

The result of a subquery that returns no values is NULL. If a subquery returns NULL, the query did not retrieve any information.

Subquery syntax

Always enclose the SELECT statement of a subquery in parentheses. The subquery's SELECT statement has a SELECT syntax that is somewhat restricted, as shown by its syntax:

(SELECT [ALL | DISTINCT ] subquery_select_list 
  [FROM  [[database.]owner.]{table_name |view_name}
 [({INDEX index_name | PREFETCH SIZE |[LRU|MRU]})]}
        [HOLDLOCK | NOHOLDLOCK] [SHARED]
     [,[[database.]owner.]{table_name |view_name}
 [({INDEX index_name | PREFETCH SIZE |[LRU|MRU]})]}
        [HOLDLOCK | NOHOLDLOCK] [SHARED]]... ] 
 [WHERE search_conditions] 
 [GROUP 
     BY aggregate_free_expression [, 
        aggregate_free_expression]... ] 
[HAVING search_conditions])

Subquery restrictions

A subquery is subject to the following restrictions:

Subquery Examples

SET ROWCOUNT 1  -- Return only 1 row

SELECT tb1.c1
  FROM table1 tb1
 WHERE EXISTS ( SELECT 1
                  FROM table2 tb2
                 WHERE tb2.c2 = tb1.c2 )

SET ROWCOUNT 0  -- Return all rows
go
31081663

SET ROWCOUNT 1

SELECT tb1.c1
  FROM table1 tb1,
       table2 tb2
 WHERE tb2.c5 = ( SELECT MAX(c5) 
                    FROM table2 )
   AND tb2.c4 = tb1.c4

SET ROWCOUNT 0
go
45298231

Correlated Subqueries

A correlated subquery is a subquery whose WHERE predicate includes joining conditions on an outer table.

Example:

/* In all examples we make reference to the following two tables. */
CREATE
 TABLE test_table1(
       column01 INT NOT NULL,
       column02 INT NOT NULL,
       column03 CHAR(20) NOT NULL,
 CONSTRAINT ix01_test_table1
   PRIMARY KEY CLUSTERED (column01)
)
LOCK DATAROWS
go

CREATE
 TABLE test_table2(
       column01 INT NOT NULL,
       column02 INT NOT NULL,
       column03 CHAR(20) NOT NULL,
 CONSTRAINT ix01_test_table2
   PRIMARY KEY CLUSTERED (column01)
)
LOCK DATAROWS
go

CREATE NONCLUSTERED INDEX ix02_test_table2
ON test_table2 (column02, column03)
go

SELECT t1.column02,
       t1.column03
  FROM test_table t1
 WHERE EXISTS ( SELECT 1
                  FROM test_table2 t2
                 WHERE t1.column01 = t2.column01
                   AND t1.coumn02 != t2.column02 )
go

How can I use a subquery to return results from test_table1 if and only if test_table2 contains a matching record on column02 of test_table1?

SELECT t1.column01,
       t1.column02,
       t1.column03
  FROM test_table t1
 WHERE EXISTS ( SELECT 1
                  FROM test_table2
                 WHERE t1.column02 = t2.column02 )
go

Joint field subtractions

How can I use a subquery to return results from test_table1 if and only if test_table2 does not contain a matching record on column02 of test_table1?

SELECT t1.column01,
       t1.column02,
       t1.column03
  FROM test_table t1
 WHERE NOT EXISTS ( SELECT 1
                      FROM test_table2
                     WHERE t1.column02 = t2.column02 )
go

How can I use a subquery to return results from test_table1 if and only if test_table2 contains a match on every field for every record of test_table1?

SELECT t1.column01,
       t1.column02,
       t1.column03
  FROM test_table t1
 WHERE EXISTS ( SELECT 1
                  FROM test_table2
                 WHERE t1.column01 = t2.column01
                   AND t1.column02 = t2.column02
                   AND t1.coumn03 = t2.column03 )
go

Joint record subtractions

How can I use a subquery to return results from test_table1 if and only if test_table2 does not contain a match on every field for every record of test_table1?

SELECT t1.column01,
       t1.column02,
       t1.column03
  FROM test_table t1
 WHERE NOT EXISTS ( SELECT 1
                      FROM test_table2
                     WHERE t1.column01 = t2.column01
                       AND t1.column02 = t2.column02
                       AND t1.coumn03 = t2.column03 )
go

DisJoint field intersections

How can I use a subquery to return results from test_table1 if and only if test_table2 contains a matching record on column02 of test_table1?

SELECT t1.column01,
       t1.column02,
       t1.column03
  FROM test_table t1
 WHERE EXISTS ( SELECT 1
                  FROM test_table2
                 WHERE t1.column02 = t2.column02 )
go

DisJoint field subtractions

How can I use a subquery to return results from test_table1 if and only if test_table2 does not contain a matching record on column02 of test_table1?

SELECT t1.column01,
       t1.column02,
       t1.column03
  FROM test_table t1
 WHERE NOT EXISTS ( SELECT 1
                      FROM test_table2
                     WHERE t1.column02 = t2.column02 )
go

DisJoint record intersections

How can I use a subquery to return results from test_table1 if and only if test_table2 contains a match on every field for every record of test_table1? The answers to these questions are exactly the same whether a set is joint or disjoint.

SELECT t1.column01,
       t1.column02,
       t1.column03
  FROM test_table t1
 WHERE EXISTS ( SELECT 1
                  FROM test_table2
                 WHERE t1.column01 = t2.column01
                   AND t1.column02 = t2.column02
                   AND t1.coumn03 = t2.column03 )
go

DisJoint record subtractions

How can I use a subquery to return results from test_table1 if and only if test_table2 does not contain a match on every field for every record of test_table1?

SELECT t1.column01,
       t1.column02,
       t1.column03
  FROM test_table t1
 WHERE NOT EXISTS ( SELECT 1
                      FROM test_table2
                     WHERE t1.column01 = t2.column01
                       AND t1.column02 = t2.column02
                       AND t1.coumn03 = t2.column03 )
go

NonCorrelated subqueries

A Non-Correlated subquery is a subquery whose WHERE predicate does not include joining conditions on any outer table. Non-Correlated subqueries are often used to determine if some aggregate property is true.

Example:

/* In all examples we will make reference to the following two tables. */
CREATE
 TABLE test_table1(
       column01 INT NOT NULL,
       column02 INT NOT NULL,
       column03 CHAR(20) NOT NULL,
 CONSTRAINT ix01_test_table1
   PRIMARY KEY CLUSTERED (column01)
)
LOCK DATAROWS
go

CREATE
 TABLE test_table2(
       column01 INT NOT NULL,
       column02 INT NOT NULL,
       column03 CHAR(20) NOT NULL,
 CONSTRAINT ix01_test_table2
   PRIMARY KEY CLUSTERED (column01)
)
LOCK DATAROWS
go

CREATE NONCLUSTERED INDEX ix02_test_table2
ON test_table2 (column02, column03)
go

SELECT t1.column02,
       t1.column03
  FROM test_table t1
 WHERE t1.column03 = ( SELECT MAX(t2.column03)
                         FROM test_table2 )
go

How do I display only those records from test_table1 which are equal to the summation of column02 in test_table2?

SELECT t1.column02,
       t1.column03
  FROM test_table t1
 WHERE t1.column02 = ( SELECT SUM(t2.column02)
                         FROM test_table t2 )
go

Query tuning in ASE and join types

Within ASE there are two types of joins: Nested Iteration joins and Sort Merge joins. Here is the showplan for a Nested iteration join:

    STEP 1
        The type of query is SELECT.

        FROM TABLE
            table_1
        Nested iteration.
        Table Scan.
        Forward scan.
        Positioning at start of table.
        Using I/O Size 2 Kbytes for data pages.
        With LRU Buffer Replacement Strategy for data pages.

Nested iteration

A nested iteration join, joins the OUTER TABLE to the INNER TABLE by performing a number of lookups equal to the number of records which qualify to be joined.

Nested Iteration joins can be visualized as follows:

Sort merge

A sort merge sorts the data in both result sets and scans through both result sets comparing values.

We can visualize this as follows: