Table Joins in Google Bigquery - Syntax

[INNER] JOIN

An INNER JOIN, or simply JOIN, effectively calculates the Cartesian product of the two from_items and discards all rows that do not meet the join condition. "Effectively" means that it is possible to implement an INNER JOIN without actually calculating the Cartesian product

SELECT R.* FROM Roster R
INNER JOIN PlayerStats P
ON R.LastName = P.LastName;

LEFT [OUTER] JOIN

The result of a LEFT OUTER JOIN (or simply LEFT JOIN) for two from_items always retains all rows of the left from_item in the JOIN clause, even if no rows in the right from_item satisfy the join predicate

SELECT R.* FROM Roster R
LEFT JOIN PlayerStats P
ON R.LastName = P.LastName;

LEFT indicates that all rows from the left from_item are returned; if a given row from the left from_item does not join to any row in the right from_item, the row will return with NULLs for all columns from the right from_item. Rows from the right from_item that do not join to any row in the left from_item are discarded.

CROSS JOIN

CROSS JOIN returns the Cartesian product of the two from_items. In other words, it retains all rows from both from_items and combines each row from the first from_items with each row from the second from_items

SELECT R.* FROM Roster R
CROSS JOIN PlayerStats P

RIGHT [OUTER] JOIN

The result of a RIGHT OUTER JOIN (or simply RIGHT JOIN) is similar and symmetric to that of LEFT OUTER JOIN

SELECT * FROM Roster R
RIGHT JOIN PlayerStats P
ON R.LastName = P.LastName;

nVector

posted on 22 Sep 19