Tip

Database tip: All about subqueries

Requires Free Membership to View

As site expert on SearchEnterpriseLinux.com, Mich Talebzadeh answers your questions about Sybase, the first enterprise DBMS on Linux. The following tip is a continuation of Mich's response to a user's question about different types of joins. The tip is excerpted, with permission of the author, from Talebzadeh's book Sybase Transact SQL Guidelines Best Practices (ISBN: 0975969307), co-authored by Ryan Thomas Putnam. -- Editor

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:

  • The subquery SELECT list must consist of only one column name, except in the exists subquery, in which case the asterisk (*) is usually used in place of the single column name. Do not specify more than one column name. Be sure to qualify column names with table or view names if there is ambiguity about the table or view to which they belong.
  • Subqueries can be nested inside the WHERE or HAVING clause of an outer SELECT, INSERT, UPDATE, or DELETE statement, inside another subquery, or in a SELECT list. Alternatively, you can write many statements that contain subqueries as joins; ASE processes such statements as joins.
  • In T-SQL, a subquery can appear almost anywhere an expression can be used, if it returns a single value.
  • You cannot use subqueries in an ORDER BY, GROUP BY, or COMPUTE list.
  • You cannot include a FOR BROWSE clause or a UNION in a subquery.
  • The SELECT list of an inner subquery introduced with a comparison operator can include only one expression or column name, and the subquery must return a single value. The column you name in the WHERE clause of the outer statement must be join-compatible with the column you name in the subquery SELECT list.
  • Text and image datatypes are not allowed in subqueries.
  • Subqueries cannot manipulate their results internally, that is, a subquery cannot include the ORDER BY clause, the COMPUTE clause, or the INTO keyword.
  • Correlated (repeating) subqueries are not allowed in the SELECT clause of an updatable cursor defined by DECLARE CURSOR.
  • There is a limit of 16 nesting levels.
  • The maximum number of subqueries on each side of a UNION is 16.
  • The WHERE clause of a subquery can only contain an aggregate function if the subquery is in a HAVING clause of an outer query and the aggregate value is a column from a table in the FROM clause of the outer query.
  • The sum of the maximum lengths of all the columns specified by a subquery cannot exceed 256 bytes.
  • 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
    
    Most 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 )
    go
    
    Joint 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 )
    go
    
    Joint 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.

    This was first published in August 2005

    There are Comments. Add yours.

     
    TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

    REGISTER or login:

    Forgot Password?
    By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
    Sort by: OldestNewest

    Forgot Password?

    No problem! Submit your e-mail address below. We'll send you an email containing your password.

    Your password has been sent to:

    Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.