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

SQL Data Definition Language Detailed Topics

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-07-11
dita:mapPath
vuk1628111288877.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
B035-1184
lifecycle
latest
Product Category
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;