0

Trying to convert SQL script from SQL Server T-SQL to GCP BigQuery SQL and need to maintain ansi nulls setting to ensure same results. Is there a way to make Google BigQuery return true when null = null is compared (i.e. 'null = null is true' below)?

Example for T-SQL and BigQuery showing different output:

--GCP BigQuery test:
begin
declare null1 string;
declare null2 string;
select case when null1 = null2 then 'null = null is true' 
       else 'null = null is false'
       end as bqsqlnulltest;
end 

-- T-SQL (SQL Server) test:
set ansi_nulls off;
declare @null1 varchar(30);
declare @null2 varchar(30);
select case when @null1 = @null2 then 'null = null is true' 
else 'null = null is false'
end as tsqlnulltest;
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
pmo511
  • 569
  • 3
  • 9
  • Your question is a little strange. `null = null` returns "true" in no database, including SQL Server and BigQuery -- well, unless you fiddle with settings that you should not change. – Gordon Linoff Jul 02 '21 at 22:43
  • `set ansi_nulls off;` should be avoided anyway, there are better methods – Charlieface Jul 04 '21 at 01:35
  • cutting over from t-sql script that relies on ansi_nulls off, trying to replicate logic (results) in bigquery (regardless of what best practice may be). – pmo511 Jul 06 '21 at 18:26

1 Answers1

1

I'm not sure exactly where this fits into your query. But you can use this construct:

where (x = y) is not false

Or in your case:

where (null1 = null2) is not false
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I have join conditions that require null = null to be true, if I understand what you're suggesting, putting that on a join will perform a cartesian join and not return what SQL Server returns [or maybe a not(x = y)]. It would also mean a lot of code edits that i'm trying to avoid. – pmo511 Jul 02 '21 at 22:19