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:
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 ...
To continue reading for free, register below or login
To read more you must become a member of SearchEnterpriseLinux.com
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
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:
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?
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?
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?
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?
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?
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?
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.
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?
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:
How do I display only those records from test_table1 which are equal to the summation of column02 in test_table2?
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:
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:
[IMAGE]
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:
[IMAGE]
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.
