Database Tutorial

Description

A single row subquery is a close relative in SQL to a pass-by-value function. Unlike a scalar subquery that returns a single value, a single row subquery returns a structure. The record structure of a single row subquery is a comma delimited list of columns or values. Pass-by-value functions are black boxes. Black boxes let you put some raw material in, process it, and get some related series of processed products out. The difference between a scalar and single row subquery is the return data type.

A single row subquery is independent of its outer query. Consider that a query with subqueries is like an inverted tree. The outermost query is the root node of the inverted tree. Any subqueries of the root node are branches, and subqueries of the first generation of subqueries are branches from branches. The lowest subquery in any sequence of branches is also known as a leaf node. Leaf nodes resolve first, followed by their siblings. After leaf nodes, the branch resolves that is their parent, the parent of the parent and so forth until all values are passed to the outermost subquery. If there’s another branch with leaf nodes, the behavior is repeated until all branches resolve with values to the outermost query. At this point the outermost query runs. This is why scalar subqueries are considered inside out.

The following demonstrates a single row subquery. The outer query depends on the result returned by the subquery. The subquery uses a first name to filter the result set, and hopefully returns a single row of data. Naturally, this is just a sample program and the question could be resolved with a simple WHERE clause and doesn’t require a subquery.

SELECT   c.first_name||' '||c.last_name AS full_name
FROM     contact c
WHERE   (c.first_name, c.last_name) =
         (SELECT   ic.first_name, ic.last_name    -- Single row subquery.
          FROM     contact ic
          WHERE    ic.first_name = 'Randi');

If the subquery returns two rows, the query would fail with an ORA-01427 error. The error means that a single row subquery returns more than one row. This type of query architecture is risky because it is data centric. The occurrence of two first names that are alike would cause this subquery architecture to fail. It relies on the subquery returning a row of data that is only found once in the result set of a subquery. A multiple row subquery is typically a better solution when the number of row returns is uncertain because it can inspect multiple row returns with an IN or other multiple row comparison operator.

In some implementations, like Oracle, you can also return a User-Defined Type (UDT) as part of the record structure. UDTs are typically objects, arrays, or lists, and they are specific to object-relational technology – more or less an Oracle database.

You eliminate the VALUES clause when you use a subquery in an INSERT statement. Parentheses around the subquery are optional in Oracle and precluded in MySQL. While a single row subquery works inside an INSERT statement, so do multiple row subqueries. There is no real difference in the syntax between the two, and a multiple row subquery raises no exception. A multiple row subquery merely inserts more than one row.

Like the scalar query, a single row subquery lets you look for something in the database to insert into another table. The single row subquery relies on business rules to find the record set for the insertion. Business rules are typically more human friendly than mapping surrogate keys between tables. They also let you develop effective queries to find data.

The following INSERT statement demonstrates a single row subquery that provides the required columns for a row insert into the item table.

INSERT INTO item
( SELECT  item_s1.nextval                         -- Single row subquery.
  ,      'ASIN: B0001US8F8'
  ,      (SELECT  common_lookup_id                -- Leaf node scalar subquery.
          FROM    common_lookup
          WHERE   common_lookup_type = 'DVD_WIDE_SCREEN')
  ,      'Around the World in 80 Days'
  ,      'Two-Disc Special Edition'
  ,      'NR'
  ,      'MPAA'
  ,      '18-MAY-2004'
  ,       3
  ,       SYSDATE
  ,       3
  ,       SYSDATE
  FROM    dual);

The single row subquery above creates a row of data that also includes a nested scalar subquery or SQL expression. The SQL expression looks up the surrogate key based on the human friendly definition of DVD_WIDE_SCREEN, and returns a foreign key value. The returned values becomes part of the record structure of the root query. You should note that the list of return columns in the SELECT clause includes only string and numeric literals along with the SQL expression. While this could just as easily be written with a VALUES clause, it illustrates the use of a subquery in an INSERT statement.

As mentioned, the scalar subquery runs first as the leaf node, and it’s value is added to the list of values in the outer query. The outer query result set is then passed to the INSERT statement for processing.

In some implementations, like Oracle, you can also return a User-Defined Type (UDT) as part of the record structure. UDTs are typically objects, arrays, or lists, and they are specific to object-relational technology – more or less an Oracle database.

The following demonstrates a single row subquery in the context of an UPDATE statement.

UPDATE  contact c
SET    (c.first_name,c.last_name) = (SELECT 'Lori','Winn' FROM dual)
WHERE  (c.first_name,c.last_name) = (SELECT 'Randi','Winn' FROM dual);

There are two single row subqueries in the sample UPDATE statement. One supports the assignment of new values in the SET clause, while the other filters the data in the WHERE clause. Both subqueries work with assigning or comparing a set of two column values.

You could rewrite the single row subquery statement with scalar subqueries, but it means each single row subquery activity becomes two scalar subqueries. The next example shows how you could implement scalar subqueries to do it. Remember, it’s only an example of what you shouldn’t do. It sole purpose is to show you the power of single-row subqueries.

UPDATE  contact c
SET     c.first_name = (SELECT 'Lori' FROM dual)
,       c.last_name  = (SELECT 'Winn' FROM dual)
WHERE   c.first_name = (SELECT 'Randi' FROM dual)
AND     c.last_name  = (SELECT 'Winn' FROM dual);

The advantage of comparing comma separated lists is a tremendous feature of single row subqueries. It is a technique often not fully understood, and seldom exploited frequently enough in real world SQL code.

In some implementations, like Oracle, you can also return a User-Defined Type (UDT) as part of the record structure. UDTs are typically objects, arrays, or lists, and they are specific to object-relational technology – more or less an Oracle database.

You typically use a single row subquery when you lookup a record structure, as a set of column values from another table or collections of tables in a filtered join. The lookup follows a business rule that can’t easily be done by using manual inspection of the data. Below is an example of a DELETE statement that compares a record structure returned by a subquery.

DELETE
FROM   contact c
WHERE (c.first_name, c.last_name) = (SELECT 'Randi','Winn' FROM dual);

The preceding example uses a single-row subquery in an DELETE statement follows below. The subquery fabricates a row for one that may not exist in a database table.

Like the single-row subquery in the foregoing UPDATE statement section, you can rewrite this row selection into two scalar subqueries. As mentioned in that section, there’s no purpose in doing so except for demonstrating the concept. A single-row subquery is a better relative solution between the two.

DELETE
FROM   contact c
WHERE  c.first_name = (SELECT 'Randi' FROM dual)
AND    c.last_name  = (SELECT 'Winn' FROM dual);

Both scalar subqueries must complete before the comparisons can be made in the WHERE clause. If both single comparison conditions are met, the query returns qualified rows. It is the equivalent of writing two scalar subqueries.

In some implementations, like Oracle, you can also return a User-Defined Type (UDT) as part of the record structure. UDTs are typically objects, arrays, or lists, and they are specific to object-relational technology – more or less an Oracle database.