Why does Oracle EXCEPT operation produce NULL values when source tables have no NULLs?

Data and Databases
1

I’m encountering an odd problem with Oracle’s EXCEPT operator (MINUS). When I create a table using this query:

CREATE TABLE ResultData AS
    SELECT X,Y,Z FROM FirstTable
    EXCEPT
    SELECT X,Y,Z FROM SecondTable

The new table ends up having NULL values in column X, but neither FirstTable nor SecondTable actually contains any NULL values in that column.

However, when I run this query to validate the results:

SELECT * FROM
(
   SELECT X,Y,Z FROM FirstTable
    EXCEPT
    SELECT X,Y,Z FROM SecondTable
) 
WHERE X IS NULL

It returns zero rows! This contradiction seems quite peculiar to me.

Here’s my actual scenario:

I created a table named DataDiff using a complex EXCEPT query that compares records from live_db.purchase_orders_fact with archive_db.purchase_orders_fact. The query includes EXISTS clauses checking related tables with date filters and status conditions.

After creating the table, I ran:

SELECT COUNT(*) from DataDiff WHERE ORDER_ID IS NULL

This returned 23 rows containing NULL ORDER_ID values.

Yet when I execute the same EXCEPT query with a WHERE clause for NULL ORDER_ID values, I get 0 rows returned.

This is part of a data archival verification process. The system should confirm that archived data matches the source, but these unexpected NULL values are creating misleading differences. How can NULL values show up in the created table when they do not exist in either source table?

2

Sounds like you’ve got implicit commits happening when the table gets created. Oracle’s EXCEPT gets weird with concurrent activity on source tables. Wrap your CREATE TABLE in an explicit transaction - that should fix the consistency problem.

3

This occurs due to Oracle’s transaction isolation and potential timing issues during table creation and data verification. When using CREATE TABLE AS SELECT with EXCEPT, Oracle captures a snapshot of the data at that moment. If changes occur in the source tables simultaneously, you may see inconsistent results. Additionally, implicit data type conversions or joins in your complex query may introduce NULLs during the table creation process, even if the source columns are correct. To troubleshoot, run the EXCEPT query independently and check the results immediately. Ensure that no triggers or computed columns are manipulating your data during table creation. Implementing explicit NOT NULL constraints in your source query could also help identify where these NULL values are originating.