SQL Basics
Cross Join – No Criteria Required
CROSS JOIN
operation will return the Cartesian product of the two tables regardless of a common value
Left Outer Join versus Right Outer Join
Left Outer join returns ALL left table rows, regardless of whether they match criteria or not. Similarly for RIGHT outer join.
Distinct (Remove Duplicates)
Include the distinct keyword in your select statement.
GROUP BY
Group by does not remove duplicates – just groups by the occurrence of a non-distinct value.
Order BY – is the last part of any query.
AVG
Returns either avg or NaN
WITH
The WITH
clause contains one or more named subqueries that execute every time a subsequent SELECT
statement references them.
RAND
generates a pseudo-random value of type FLOAT64 in the range of (0, 1)REGEXP_REPLACE
returns a string where all substrings of the input value that match the input regular expression are replaced with the replacement value. Let’s see an example where we replace every “coffee” occurrence with “tea:
SPLIT
SPLIT
splits the input value using the given delimiter. For STRING
input value, the default delimiter is the comma. Splitting an empty STRING
input returns an ARRAY with a single empty STRING
.
re |
---|
Coffee, or, Tea?] |
ARRAY_TO_STRING
ARRAY_TO_STRING
transforms arrays to strings by concatenating all of the elements. This function takes an array and a concatenation value as an input, like this:
DATETIME_DIFF
DATETIME_DIFF
calculates the number of whole specified intervals between two DATEΤΙΜΕ objects.
IFNULL
IFNULL
evaluates the expression within and, if it’s NULL
, then it returns the given result. Otherwise, the conditional expression returns the result of the included expression. Let’s see an example of a NULL
evaluation:
Leave a Reply