Category: ASE: T-SQL: DQL

Subqueries, inner queries

To create more complex queries, it might be necessary to incorporate queries into a query condition. Such a query is known as subquery. So in other words a query within a query. Subqueries are thus also called inner queries. Select statements containing a subquery within a subquery are described as nested queries or nested select statements, also als verschalchtelte Abfragen bezeichnet. The level of nesting within a single statement depends on the version of ASE. Version 12.5 for example permits a nesting level of 16. Starting with version 15.0 up to 50 subqueries can be nested into each other.

Subqueries: applications

Using where and having clauses queries are written in a way that the resultset is restricted according to the conditions set in the clause. Sometimes, the result depends on information stored in more than one table. Subqueries inside a where or having clause can also be used to formulate a query the result of which depends on the output of another query. The same result could be achieved using a join. In general, subqueries are easier to formulate than joins though. However, the number of columns and rows returned by a subquery is restricted.

Subqueries with only one return value can also be defined in select and from statements and also in exists, in, any and all clauses. exists can only be used in a if or while statement. Inside in, any and all clauses, subqueries can even return more than one value, as long as they are stored in one column only.

Subqueries in awhere clause

    select * from sysdevices
      where id = (select id from sysdatabases where name = db_id("db_name"))
    

Subqueries in a having clause

'having' clauses use subqueries to organize the row structure of group by clauses.

Subqueries in an exists clause

A subquery in an exists clause can only be used in an if or a while statement. The resultset of the subquery must contain at least one row in order to return true.

Subqueries in an in clause

Inside an in clause subqueries can return several values, as long as the are stored in one column. The resultset of a subquery must contain at least one row in order to return true.

    select * from sysprocesses
      where suid in
        (select suid from syslogins where name = 'user_name')
    

This subquery can be used to return details on a user process.

Subqueries in an all clause

A subquery in an all clause can only be used in connection with the logical operators larger than(>) or less than(<). All rows must match the condition.

Subqueries in an any clause

A subquery in an anyclause can only be used in connection with the logical operators equal =, not equal <>, less than <, less or equal <=, larger than > and larger or equal >= The result of the subquery must contain at least one row in order to return true.

Subquery: Types

There are two different types of subquery. expression subqueries and quantified predicate subqueries. Each of these types can be subdivided into the types noncorrelated subqueries or correlated subqueries.