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

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

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.

The second statement is coded with a WHERE clause that tests FALSE. This clause makes sure 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 the following 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 as the first CREATE TABLE statement, the preceding statement produces a table kind of SET for target_table, because its source is a subquery 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;