SQL

CAST

SQL

CASTing with SQL # SAFE_CAST # Replace runtime errors with nulls SELECT SAFE_CAST(<field> AS <type>)

Concatenation

SQL

SQL Concatenation # There are a few ways to do it (and it varies by SQL dialect) SELECT 'a' || 'b' as concatenated -- returns 'ab'

NULLIF

SQL

SQL NULLIF # Null if two values are equivalent. Otherwise, return the first. SELECT NULLIF('a', 'a');

Union

SQL

SQL Unions # Stack data on top of each other. Union # Concatenates records, drops duplicates. For instance, when there are similar columns/fields across two tables, and same data types: SELECT <fields> FROM <table_1> UNION SELECT <fields> FROM <table_2>; Union All # Concatenates records, keeps duplicates.

WITH

SQL

SQL WITH # WITH clause defines a temporary dataset (i.e., not saved anywhere, only exists during the scope of the query) that can be called by later queries. The construct is: WITH <temporary object name> AS (...) ... WITH temp_data AS ( SELECT A, SUM(B) as total FROM main_table GROUP BY A ) SELECT AVG(total) average_of_individual_totals FROM temp_data Different from Subqueries # WITH does seem to operate like a subquery. ...