SQL Basics

Inner Join (requires a matching criteria)

SELECT *

FROM A

INNER JOIN B

ON A.a = B.c

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.

WITH SubQ1 AS (

SELECT first_name

FROM CLIENTS

)

SELECT *

FROM subQ1

Random Numbers

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:

SELECT

REGEXP_REPLACE(“Hot Coffee or Cold Coffee?”, “coffee”, “tea”) AS re

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.

SELECT SPLIT(“Coffee or Tea?”, ” “) AS re

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:

SELECT ARRAY_TO_STRING([“a”, “b”], “–“) AS array_to_string

DATETIME_DIFF

DATETIME_DIFF calculates the number of whole specified intervals between two DATEΤΙΜΕ objects.

SELECT DATEΤΙΜΕ_DIFF(DATEΤΙΜΕ ‘2021-11-29 14:24:44’, DATETIME ‘2018-11-28 17:22:42’, HOUR) AS hour_difference

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:

SELECT IFNULL(NULL, ‘C’) AS result

Anuj holds professional certifications in Google Cloud, AWS as well as certifications in Docker and App Performance Tools such as New Relic. He specializes in Cloud Security, Data Encryption and Container Technologies.

Initial Consultation

Anuj Varma – who has written 1216 posts on Anuj Varma, Hands-On Technology Architect, Clean Air Activist.