15.00 - Misleading Similarities of WITH DATA and WITH NO DATA Clauses - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1184-015K

Misleading Similarities of WITH DATA and WITH NO DATA Clauses

Because of the rules governing what is or is not copied to a target table during a CREATE TABLE … AS operation, some requests 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 request is equivalent to the CREATE TABLE … AS … WITH DATA request that follows it because both use the same subquery to define the columns in target_table.

Note that the second request is coded with a WHERE clause that always tests FALSE. This rather inelegantly coded 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 request is equivalent to the paired CREATE TABLE … AS … WITH NO DATA request and INSERT … SELECT requests that follow because the CREATE TABLE … AS … WITH NO DATA request copies the exact table definition from source_table and the INSERT … SELECT then populates target_table with all 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 request is not equivalent to the second because the subquery in the second request 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” on page 660).

    CREATE TABLE target_table AS source_table
    WITH NO DATA;

If source_table is MULTISET and you issue this CREATE TABLE request 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 request, this request 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 request is not equivalent to the second because the subquery in the second request 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” on page 660).

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

Related Topics

For information about the FROM TABLE option, which is similar to the WITH DATA AND STATISTICS option for CREATE TABLE … AS, see “COLLECT STATISTICS (Optimizer Form)” on page 175.

For information about the syntax used to copy a table definition and its data and statistics, see “CREATE TABLE” in SQL Data Definition Language Syntax and Examples.

For information about using CREATE TABLE (AS Clause) with ALTER TABLE to modify existing tables, see “ALTER TABLE (Basic Table Parameters)” on page 31.

For syntax information related to using CREATE TABLE (AS Clause) to modify existing tables, see “ALTER TABLE” in SQL Data Definition Language Syntax and Examples.