MacLochlainns Weblog

Description

A scalar subquery is one of the closest relative SQL has to a traditional pass-by-value function. A pass-by-value function can also be described as a black box, where you put some raw material in and get some processed product out.

A scalar 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 queries.

The following demonstrates two nested scalar subqueries. The outer query depends on the result returned by the subquery to the address table. While the subquery against the address table depends on the result returned by the subquery to the telephone table.

SELECT   first_name||' '||last_name
FROM     contact
WHERE    contact_id =
          (SELECT   a.contact_id                  -- An intermediate branch subquery.
           FROM     address a
           WHERE    a.address_id =
            (SELECT   t.address_id                -- A leaf node subquery.
             FROM     telephone t
             WHERE    t.telephone_id = 1001));

If either of the two subqueries returns no more than one row, the query would fail with an ORA-01427 error. The error means that a single row subquery returned more than one row. The scalar subquery architecture is risky because it is data centric, and it relies on both subqueries returning a single key that has only one parent. A multiple row subquery is a more flexible solution when coupled with an IN or other multiple row comparison operator.

You typically use a scalar subquery when you lookup a primary key to insert it as a foreign key column. An example of a scalar subquery would be looking up the common_lookup_id for a DVD, as shown below:

INSERT INTO item VALUES
( item_s1.nextval
,'ASIN: B0001US8F8'
,(SELECT   common_lookup_id                       -- Primary key scalar query lookup.
  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);

The scalar subquery above looks up a surrogate key based on a human friendly definition of DVD_WIDE_SCREEN. Actually, that value isn’t too human friendly. The form or web page would actually present something like DVD Wide Screen, which would then map to the uppercase string shown in the example. You would need to define both columns in a table like the common_lookup_table table. Then, every row can map and substitute the end-user friendly and code reusable values or vice versa.

In some implementations, like Oracle, you can also return a User-Defined Type (UDT). 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 scalar subquery when looking up a single column value from another table or collections of tables. The SET clause generally requires a scalar subquery or at least a single row subquery, but doesn’t impose a single row subquery constraint in all cases. You can use a multiple row subquery when you correlate the rows of a subquery with the rows of an UPDATE statement.

A pair of scalar subqueries are shown in the UPDATE statement.

UPDATE rental_item ri
SET    ri.rental_id = (SELECT   r.rental_id       -- A scalar subquery assignment.
                       FROM     rental r
                       ,        contact c
                       WHERE    r.customer_id = c.contact_id
                       AND      c.last_name = 'Winn'
                       AND      c.first_name = 'Randi')
WHERE  ri.rental_id = (SELECT   r.rental_id       -- A scalar subquery comparison.
                       FROM     rental r
                       ,        contact c
                       WHERE    r.customer_id = c.contact_id
                       AND      c.last_name = 'Winn'
                       AND      c.first_name = 'Brian');

The two scalar subqueries in the UPDATE statement accomplish the same type of thing but in two different contexts. One works in the assignment of a new value through the SET clause, while the other filters the rows to update in the WHERE clause. You should note that both subqueries run before the outer query (in this case an UPDATE statement). Scalar subqueries either assign values or compare values in an UPDATE statement, and they assign or compare values through an equality, =, operator.

A scalar subquery is data centric in this type of solution. The statement would throw an ORA-01427 error when two or more rows are returned by a subquery. A multiple row subquery is more flexible and data set independent when you combine it with an IN or other multiple row comparison operator.

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

You use a scalar subquery when looking up a single column value from another table or collections of tables. An example of a scalar subquery follows in a DELETE statement.

DELETE
FROM   rental_item ri
WHERE  ri.rental_id = (SELECT   r.rental_id       -- A scalar subquery comparison.
                       FROM     rental r
                       ,        contact c
                       WHERE    r.customer_id = c.contact_id
                       AND      c.last_name = 'Winn'
                       AND      c.first_name = 'Brian');

The scalar subquery in the WHERE clause of the DELETE statement runs first. The subquery returns a single scalar value. The DELETE statement uses the value returned from the scalar subquery to filter which rows are deleted from a table. You should note that the subquery compares values through an equality, =, comparison operator.

A scalar subquery is data centric in this type of solution. The statement would throw an ORA-01427 error when two or more rows are returned by a subquery. A multiple row subquery is more flexible and data set independent when you combine it with an IN or other multiple row comparison operator.

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

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 subqueries. 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.

Description

Like the scalar and single-row subquery, multiple row subqueries are close relatives in SQL to a pass-by-value function. Unlike those seen earlier in the blog page, they return a two-dimensional structure instead of a scalar variable or record structure. As discussed, a pass-by-value function can also be described as a black box. In the case of a multiple row subquery, you put some raw material in and get a table of processed products out.

You typically use a multiple row subquery when you lookup a related set of information that has more than one row. It is important to note that all columns must return the same number of rows of data. For example, you can’t use a scalar subquery in the select list of a multiple row subquery without raising an ORA-01427 error because it returns only one row by definition. This is the opposite of data centric errors from single-row subqueries when the nested query actually returns more rows than the base query.

The following base subquery returns string and numeric literals for all but one column, which is returned by what is hoped to be a scalar subquery. Unfortunately, the business logic is fatally flawed and the subquery returns three rows.

SELECT 'ASIN: B0001US8F8'
,      (SELECT  common_lookup_id                  -- Actually returns more than one row.
        FROM    common_lookup
        WHERE   common_lookup_type LIKE 'DVD%')
,      'Around the World in 80 Days'
,      'Two-Disc Special Edition'
,      'NR'
,      'MPAA'
,      '18-MAY-2004'
,       3
,       SYSDATE
,       3
,       SYSDATE
FROM    dual;

The imbalance between the number of rows returned between columns, triggers an ORA-01427, as noted below.

,      (SELECT  common_lookup_id
        *
ERROR at line 3:
ORA-01427: single-ROW subquery RETURNS more than one ROW

You can fix this type of problem without a great deal of difficulty. You simply cross join the queries, which produces a product of the rows of one times the rows of the other. This technique balances the select list elements.

SELECT 'ASIN: B0001US8F8'
,       il.common_lookup_id                       -- Column value from inline view.
,      'Around the World in 80 Days'
,      'Two-Disc Special Edition'
,      'NR'
,      'MPAA'
,      '18-MAY-2004'
,       3
,       SYSDATE
,       3
,       SYSDATE
FROM    dual CROSS JOIN (SELECT  common_lookup_id
                         FROM    common_lookup
                         WHERE   common_lookup_type LIKE 'DVD%') il;

The multiple row subquery creates more than one row of data by cross joining against what was a subquery in the previous example. The subquery becomes an inline view to resolve the row imbalance. You create a repeating group of the base query through the multiplication property of the cross join or Cartesian product. This technique lets you resolve the row imbalanced.

You typically use a multiple row subquery when you lookup a related set of information that has more than one row. In an INSERT statement, a multiple row subquery performs much like a single-row subquery. It replaces the VALUES clause with a SELECT statement. It is important to note that all columns must return the same number of rows of data. For example, you can’t use a scalar subquery in the select list of a multiple row subquery without raising an ORA-01427 error because it returns only one row by definition. This is the opposite of data centric errors from single-row subqueries when the nested query actually returns more rows than the base query.

The following base subquery is the sample used previously in the description of multiple row subqueries. It returns string and numeric literals for all but one column, which makes the base query a single-row subquery. The non-compliant column is actually a multiple row subquery. While a query may return only one row with test data, its actual performance may return more than one row. When a planned query performs in an unanticipated way, the business logic that formed it is fatally flawed. This is the case in the example because the subquery returns two rows.

INSERT INTO item
( SELECT  item_s1.nextval
  ,      'ASIN: B0001US8F8'
  ,      (SELECT  common_lookup_id
                           FROM    common_lookup
                           WHERE   common_lookup_type LIKE 'DVD%')
  ,      'Around the World in 80 Days'
  ,      'Two-Disc Special Edition'
  ,      'NR'
  ,      'MPAA'
  ,      '18-MAY-2004'
  ,       3
  ,       SYSDATE
  ,       3
  ,       SYSDATE
  FROM    dual);

The imbalance between the number of rows returned between columns, triggers an ORA-01427, as noted below.

SQL> /
  ,      (SELECT  common_lookup_id
          *
ERROR at line 4:
ORA-01427: single-ROW subquery RETURNS more than one ROW

You can fix this type of problem without a great deal of difficulty. As you did in the previous query only example, you simply cross join the queries. The cross join produces a Cartesian product of the rows, and effectively balances row returns of the select list elements.

INSERT INTO item
( SELECT  item_s1.nextval
  ,      'ASIN: B0001US8F8'
  ,       il.common_lookup_id
  ,      'Around the World in 80 Days'
  ,      'Two-Disc Special Edition'
  ,      'NR'
  ,      'MPAA'
  ,      '18-MAY-2004'
  ,       3
  ,       SYSDATE
  ,       3
  ,       SYSDATE
  FROM    dual CROSS JOIN (SELECT  common_lookup_id
                           FROM    common_lookup
                           WHERE   common_lookup_type LIKE 'DVD%') il);

When you use this technique, the query that returns one row and the query that returns many rows are matched. This happens because a cross join effectively multiplies the one side of the relationship by the number of rows on the many side of the join.

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, so you see 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.

You typically use a single row subquery when you lookup a set of column values from another table or collections of tables in a filtered join. The lookup follows a business rule that mere mortals can remember. An example of a scalar subquery in an DELETE statement with table fabrication is shown below:

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

Like the single row subquery in an UPDATE statement, you can rewrite this deletion to use two scalar subqueries. The example is:

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

The single row subquery in the WHERE clause of the DELETE statement runs first. It returns a single row with multiple values, and is the equivalent of writing two scalar subqueries.