page37
17
always put the smallest table first when joining tables, noting tables may grow
over time.
You might also see
JOIN
criteria to reference further tables, regardless of whether
a single table or multiple tables. A lookup is performed to ensure specified named
object(s) exist within the metadata repository. The
FROM
and
JOIN
clauses provide the
total accessible data set for the query referencing the attributes (table columns) and rows
(the actual data).
I prefer to fully qualify object names using
database.schema.object
notation to
prevent ambiguity when referencing source objects.
WHERE Clause
The
FROM
clause identifies the full scope of accessible objects and attributes for the
query. The
WHERE
clause—also known as the predicate—identifies how the in-scope
objects relate to each other, also defining filters or constraints.
You might see multiple predicates applied using
AND / OR
syntax; each is a filter or
constraint to the returned data set.
Predicates implement join conditions between tables and are essential for
developing optimally performant code. Where two or more objects are accessed and not
joined, a Cartesian product (also called a join explosion) results.
Failure to join tables in a
WHERE
clause results in a Cartesian product.
When generating high volumes of test data, you might deliberately choose to
omit a join condition. This is a rare but acceptable use case noting the performance
implications.
GROUP BY Clause
When aggregating attributes, you add a
GROUP BY
clause. Grouping ensures the returned
data set contains rows equal to the unique values in that column.
ChaptEr 1
tuning thE SnowFlakE Data ClouD