CODEWITHSUNDEEP
sqlr
stats_noob
stats_noob

Reputation: 5925

Netezza SQL: Check if two tables are identical

I am working with Netezza SQL.

Suppose I have two tables:

### TABLE1

  var1 var2
1    1    a
2    1    b
3    1    c

## TABLE2

  var1 var2
1    1    a
2    1    b
3    1    c

If I was using the R programming language, I could have easily verified that these two tables are identical:

> identical(table1, table2)
[1] TRUE

My Question: Is it possible to do something similar in Netezza SQL?

Thanks!

Upvotes: 2

Views: 55

Answers (1)

keraion
keraion

Reputation: 1182

Set operators can be used to find the differences between two tables.

EXCEPT ALL removes the same rows from the second result set from the first result set, this has to be run in both orders to get all differences. UNION ALL merges the two results sets together. If any rows are returned, they aren't the same. COUNT(*) = 0 will return this as a boolean.

-- Example data and table names with a CTE, this isn't required.
WITH TABLE1 (var1, var2) AS (
    VALUES (1, 'a'), (1,'b'), (1,'c')
),
TABLE2 (var1, var2) AS (
    VALUES (1, 'a'), (1,'b'), (1,'c')
)

-- Compare these tables
SELECT COUNT(*) = 0 AS IS_IDENTICAL FROM
(
    (
        SELECT * FROM TABLE1
        EXCEPT ALL
        SELECT * FROM TABLE2 
    )
    UNION ALL
    (
        SELECT * FROM TABLE2
        EXCEPT ALL
        SELECT * FROM TABLE1 
    )
)
;

Output:

IS_IDENTICAL
true

Upvotes: 2

Related Questions