CODEWITHSUNDEEP
google-bigquery
Mask
Mask

Reputation: 573

Using where clause with Union in GoogleSql Bigquery

I am trying to union two tables comparing a matching field but I am receving the following error:- "SQL_ANALYSIS_ERROR: Unrecognized name: t2 (Line: 87, Column: 121)" where t2 is renamed for my second table

Here is my code:-

CREATE OR REPLACE TABLE table_1 OPTIONS (
  quota_accounting_owner='[email protected]')
AS 
  SELECT * FROM
  (
  SELECT * EXCEPT(fate_result), CAST(fate_result AS STRING) AS fate_result FROM table_1 AS t1 WHERE t2.e_id <> t1.e_id
  UNION DISTINCT
  SELECT * EXCEPT(fate_result), CAST(fate_result AS STRING) AS fate_result FROM table_2 AS t2
    );

Upvotes: 0

Views: 592

Answers (1)

Olivier Depriester
Olivier Depriester

Reputation: 1625

You can use NOT EXISTS to select only the rows of table_1 that don't have a matching e_id in table_2

CREATE OR REPLACE TABLE table_1 OPTIONS (
  quota_accounting_owner='[email protected]')
AS 
  SELECT * FROM
  (
    SELECT * EXCEPT(fate_result), CAST(fate_result AS STRING) AS fate_result
    FROM table_1 AS t1
    WHERE NOT EXISTS (
       SELECT 1
       FROM table_2 AS t2
       WHERE t2.e_id = t1.e_id
    )
    UNION DISTINCT
    SELECT * EXCEPT(fate_result), CAST(fate_result AS STRING) AS fate_result
    FROM table_2 AS t2
  );

EDIT NOT EXISTS would be the right choice for standard SQL but seeing the comments below, here it throws an error. so NOT IN may be better

CREATE OR REPLACE TABLE table_1 OPTIONS (
  quota_accounting_owner='[email protected]')
AS 
  SELECT * FROM
  (
    SELECT * EXCEPT(fate_result), CAST(fate_result AS STRING) AS fate_result
    FROM table_1 AS t1
    WHERE t1.e_id NOT IN (
       SELECT t2.ed_id
       FROM table_2 AS t2
    )
    UNION DISTINCT
    SELECT * EXCEPT(fate_result), CAST(fate_result AS STRING) AS fate_result
    FROM table_2 AS t2
  );

Upvotes: 1

Related Questions