MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Basic SQL subqueries

with 3 comments

Subqueries are the bread and butter of making SQL effective and powerful as a programming language. They enable you to find lookup values, like primary keys. They let you validate whether values exist or don’t exist, and thereby serve to filter the result set of the master or outer query.

Subqueries are queries that run inside other queries or statements, like data manipulation statements – INSERT, UPDATE, and DELETE. The query or statement that contains a subquery is also known as an outer query, and subqueries are inner queries.

You can nest subqueries within subqueries. I’ve never found the upward limit (in practice or the documentation) but there may be one. The logic of many nested subqueries can be daunting. I’d say three or four is probably a limit that should lead you to consider alternative solutions. Some developers overtly rely on subqueries to avoid complex joins semantics.

Subquery Definitions

Summary

There are three basic types of subqueries. The simplest and sometimes known as ordinary subqueries are those that return results independent of the outer query. A correlated query runs for each row of the outer query. Correlated queries act as audits of the existence or non-existence of a row based on a join between the outer and inner query. Last, there is an inline view. Inline views are queries that produce filtered result sets, and they as run time tables.

Ordinary subqueries come in a general form as a multiple row subqueries. A single row subquery is specialized form of a multiple row subquery because it must resolve to a single row. The most specialized ordinary subquery is a SQL expression, which is a single row subquery that returns only one column. The SQL expression is more commonly referred to as a scalar subquery.

Correlated subqueries let you validate whether values exist or don’t exist, and thereby serve to filter the result set of the master or outer query. They also let you perform complex associations to conditionally update or delete multiple rows in other tables. You should have unique indexes on these join columns because correlated subqueries run for each row returned by the outer query. Unique indexes let correlated queries perform faster with fewer computing resources.

Inline views let you create filtered data sets that aren’t defined as permanent catalog views. Inline views, like catalog views, become virtual tables that let you join their contents to other tables and catalog views.

In short, subqueries fall into three categories – inside out, outside in, and bi-directional. Ordinary subqueries, or subqueries, are inside out, which means they run before the outer query and return the results to the outer query. Correlated subqueries are outside in because they run for each row returned by the outer query. Correlated subqueries match one or more outer query row’s column values against any equal number of the innner query row’s column values. Inline views are bi-directional, which means the way they’re joined is determined by the SQL cost optimizer. SQL cost optimizers are product specific to database management systems, and you can refer to this SQL join semantic blog post for more information.

joincomparison

The drawing illustrates the relationshps between queries and subqueries in the various scenarios. More or less subqueries return a value, a row of values, or a table of values, while a correlated subquery returns a Boolean acknowledgment of existence or non-existence. Inline views create filtered result sets that substitutes for a table or catalog view in a relational join pattern.

The next sections examine the specifics of the subquery types and inline view.

Under construction still … much bigger than I thought … I’ve finished the first two sections of multiple row subqueries … come back soon …

Written by maclochlainn

February 22nd, 2009 at 11:33 pm

Posted in Uncategorized

3 Responses to 'Basic SQL subqueries'

Subscribe to comments with RSS or TrackBack to 'Basic SQL subqueries'.

  1. Code Examples of these 3 types of subqueries, such as what you have for your article on inline views ( http://blog.mclaughlinsoftware.com/2008/10/19/inline-views-table-fabrication-and-the-with-clause/ ) would help me understand better what is described here.

    Steve

    8 Jun 09 at 10:54 am

  2. Never mind- I just didn’t go deep enough in the expand views to get to them. Thanks.

    Steve

    8 Jun 09 at 10:57 am

  3. hai,

    mastan

    21 Aug 12 at 1:05 am

Leave a Reply