Database tip: All about subqueries
A continuation of Mich's response to a user's question about different types of joins.
![]() |
|
![]() |
![]() |
![]() |
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 45298231Most of the previous material on subqueries was taken from Chapter 5 of the ASE 12.5 Transact-SQL User's Guide.
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 ) goJoint 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
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 ) goJoint 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?
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:
Mich Talebzadeh is a database expert with large DBA and architecture experience with special interests in Sybase and Oracle. He specializes in creating database architectures for large global trading systems involving heterogeneous databases. Mich is based in London and serves as Chairman of the Sybase Future Forum. As resident Sybase expert on SearchEnterpriseLinux.com, he can answer any of your Sybase questions.