'What does "EXCEPT distinct select * " in SQL language mean?

I am following a tutorial on Qwiklabs on Bigquery and financial fraud detection and came across a query, below, that I am failing to understand

CREATE OR REPLACE TABLE finance.fraud_data_model AS
SELECT
*
FROM finance.fraud_data_sample  
EXCEPT distinct select * from finance.fraud_data_test

I am trying to understand what is being retrieved or ignored from the finance.fraud_data_test table or what is the main finance.fraud_data_model made up of when it comes to the two other tables?

sqlgoogle-bigquery


Solution 1:[1]

The SQL EXCEPT statement is used to filter records based on the intersection of records returned via two SELECT statements.

The records that are common between the two tables are filtered from the table on the left side of the SQL EXCEPT statement and the remaining records are returned.

SELECT * FROM finance.fraud_data_sample  
EXCEPT distinct select * from finance.fraud_data_test

In your case, the query returns every record in finance.fraud_data_sample that are not present in the unique list obtained fromfinance.fraud_data_test

Solution 2:[2]

EXCEPT is a set operation, such as UNION and INTERSECT.

It returns all rows in the first table that do not have an exact match in the second, and also removes any duplicates. Because it is using SELECT *, the assumption is that the two tables have the same columns in the same order.

Solution 3:[3]

The EXCEPT keyword is used as an excluding filter for the query. In this case, it will return the rows from fraud_data_sample that are not in fraud_data_test.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 BlackMath
Solution 2
Solution 3 dan04

Related Questions