Misleading Similarities of WITH DATA and WITH NO DATA Clauses - Analytics Database - Teradata Vantage

Because of the rules governing what is or is not copied to a target table during a CREATE TABLE … AS operation, some statements that appear to be equivalent syntactically are not equivalent semantically.

This topic presents several scenarios to highlight some equivalent and non-equivalent coding.

Equivalence Set 1

The following CREATE TABLE … AS … WITH NO DATA statement is equivalent to the CREATE TABLE … AS … WITH DATA statement that follows it because both use the same subquery to define the columns in target_table.

Note that the second statement is coded with a WHERE clause that always tests FALSE. This clause ensures that only the column definitions for the subquery, and not its data, are copied to target_table.

    CREATE TABLE target_table AS
     (SELECT *
      FROM source_table
     )
    WITH NO DATA;
    CREATE TABLE target_table AS
     (SELECT *
      FROM source_table
      WHERE 1=2
     )
    WITH DATA;

Equivalence Set 2

The following CREATE TABLE … AS … WITH DATA statement is equivalent to the paired CREATE TABLE … AS … WITH NO DATA statement and INSERT … SELECT statements that follow because the CREATE TABLE … AS … WITH NO DATA statement copies the exact table definition from source_table and the INSERT … SELECT then populates target_table with all of the data from source_table.

    CREATE TABLE target_table AS (
      SELECT *
      FROM source_table)
    WITH DATA;
    CREATE TABLE target_table AS source_table
    WITH NO DATA;
    
    INSERT target_table
     SELECT *
     FROM source_table;

Non-Equivalence Set 1

The first CREATE TABLE … AS … WITH NO DATA statement is not equivalent to the second because the subquery in the second statement uses the attribute defaults defined by the table options clause. You must name attributes explicitly to define them differently than their defaults. See Using Subqueries To Customize An AS Clause.

    CREATE TABLE target_table AS source_table
    WITH NO DATA;

If source_table is MULTISET and you issue this CREATE TABLE statement in Teradata session mode, the table kind of target_table is also MULTISET.

    CREATE TABLE target_table AS (
      SELECT *
      FROM source_table)
    WITH NO DATA;

Under the same conditions cited for the first CREATE TABLE statement, this statement instead produces a table kind of SET for target_table because it uses a subquery for its source rather than a base table.

Non-Equivalence Set 2

This result parallels Non-Equivalence Set 1.

The first CREATE TABLE … AS … WITH DATA statement is not equivalent to the second because the subquery in the second statement uses defaults for attributes defined by the table options clause. You must name attributes explicitly to define them differently than their defaults (see Using Subqueries To Customize An AS Clause).

    CREATE TABLE target_table AS source_table
    WITH DATA;
    CREATE TABLE target_table AS (
      SELECT *
      FROM source_table
    WITH DATA;