17.05 - Scalar Subqueries - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

A scalar subquery is a subquery expression that can return a maximum of one value.

As with other types of subqueries, there are two types of scalar subqueries:
  • Correlated
  • Noncorrelated

A correlated scalar subquery returns a single value for each row of its correlated outer table set.

A noncorrelated scalar subquery returns a single value to its containing query.

You can think of a correlated scalar subquery as an extended column of the outer table set to which it is correlated. In the same way, you can think of a noncorrelated scalar subquery as a parameterized value.

Accordingly, you can specify a correlated scalar subquery in a statement in the same way as a column, while you can specify a noncorrelated scalar subquery in the same way as you would a parameterized value wherever a column or parameterized value is allowed. This includes specifying a scalar subquery as a standalone expression in various clauses of a DML statement, or specified within an expression.

ANSI Compliance

Scalar subqueries are compliant with the ANSI SQL:2011 standard.

Specifying Scalar Subqueries in SQL Statements

The rules and restrictions are:

  • A scalar subquery can be correlated to one or more outer tables.
  • A scalar subquery can have nested levels of subqueries, which can be either scalar or nonscalar.
  • A scalar subquery can be either correlated or noncorrelated.
  • A scalar subquery can specify joins, both inner and outer, aggregation, and other similar SQL DML statement attributes.
  • A scalar subquery specified as the right hand side operand of a predicate can return more than one column, but a scalar subquery that is specified elsewhere in the statement or that is specified within an expression must specify exactly one column or column expression in its select list.
  • A scalar subquery cannot be specified in an SQL DML statement for which scalar subqueries are not supported.

    The following DML statements support scalar subqueries.

    • ABORT
    • DELETE
    • INSERT
    • ROLLBACK
    • SELECT

      The recursive statement of a recursive query and the SELECT AND CONSUME statement do not support scalar subqueries.

    • UPDATE
    • UPDATE (Upsert Form)

      You cannot specify a correlated scalar subquery in the condition of a SET or WHERE clause for the Upsert form of UPDATE.

  • You cannot specify a scalar subquery in a statement that supports scalar subqueries, but violates a usage restriction.
  • The cardinality of a scalar subquery result cannot be greater than one.
  • If a scalar subquery returns 0 rows, then it evaluates to null.

    For example, the following WHERE clause evaluates to TRUE for those rows in salestable that do not match any rows in prodtable on prod_no:

         SELECT … FROM salestable AS s
         WHERE (SELECT 1
                FROM prodtable AS p
                WHERE s.prod_no = p.prod_no) IS NULL;
  • You cannot specify a scalar subquery in a DDL statement with the following exceptions.
    • CREATE MACRO
    • CREATE RECURSIVE VIEW

      You can specify a scalar subquery in the seed statement of a recursive view definition, but not in its recursive statement.

    • CREATE TABLE … AS
    • CREATE TRIGGER
    • CREATE VIEW
    • REPLACE MACRO
    • REPLACE TRIGGER
    • REPLACE VIEW

      You cannot specify a scalar subquery in a join index definition.

    • You cannot specify a scalar subquery in the recursive statement of a recursive statement.
    • You cannot specify a scalar subquery in a SELECT AND CONSUME statement.
    • You cannot specify a correlated scalar subquery in the condition of a SET or WHERE clause for an UPDATE (Upsert Form) statement.
    • You cannot specify a noncorrelated scalar subquery as a value that is assigned to an identity column in the value list of a simple INSERT statement.
    • You cannot specify a noncorrelated scalar subquery as a value in the value list of a simple INSERT in the body of a row trigger.
    • You cannot specify a correlated scalar subquery in the ON clause of a MERGE statement.
    • You cannot specify a correlated scalar subquery in a value list.

      For example, the following INSERT statement returns an error:

           INSERT INTO t1 (1,2,3 (SELECT d2
                                  FROM t2
                                  WHERE a2=t1.a1));

      A value list is a list of simple values that can be either constants or parameterized values. Therefore you can only specify a noncorrelated scalar subquery that returns a value of a primitive data type in a value list. You cannot specify a scalar subquery that returns a column of UDT or LOB data in a value list.

  • You can only use a forward reference to an aliased scalar subquery (SSQ) expression in the top level of the referencing expression. That is, the aliased SSQ reference cannot be nested in another SELECT block that is defined within the referencing expression. For example, an SSQ expression can contain a forward reference to an aliased SSQ expression in the select list, a WHERE clause, a GROUP BY clause, or a HAVING clause:
         SELECT (SELECT ssq2+a1 FROM t1) AS ssq1,
             (SELECT MAX(b3) FROM t3) AS ssq2
              FROM t2;

    You cannot nest the forward reference in another query block that is defined within the referencing expression. For example, the following statement, which includes a forward reference to ssq2 from the SSQ expression ssq1, is nested in a SELECT block within ssq1, and returns an error indicating incorrect use of a subquery:

         SELECT (SELECT (SEL ssq2) + a1 FROM t1) AS ssq1,
              (SELECT MAX(b3) FROM t3) AS ssq2
              FROM t2;

    To avoid the error, switch the two expressions in the select-list as shown below:

         SELECT (SELECT MAX(b3) FROM t3) AS ssq2,
              (SELECT (SEL ssq2) + a1 FROM t1) AS ssq1
              FROM t2;