page41
21
•
Implicit join notation avoids forward declaration errors.
•
To me, implicit join notation is cleaner and easier to read.
Here’s the same query using implicit join notation:
SELECT count(1)
FROM partsupp_baseline ps,
part_baseline p,
supplier_baseline s
WHERE ps.ps_partkey = p.p_partkey
AND ps.ps_suppkey = s.s_suppkey;
You will encounter this SQL statement in Chapter 3.
Forward Declaration Errors
A forward declaration error is a parsing failure and occurs when a reference is made
to an object or attribute that has not yet been declared. From the earlier discussion on
query parsing order, you know the
FROM
clause is parsed first.
•
For explicit join notation, each joined table is evaluated in
declaration order along with its joining criteria.
•
For implicit join notation, all tables are evaluated at the same time;
then all predicates are evaluated.
As a consequence of the evaluation order, it is possible to reference join keys that
have not yet been parsed but are declared later in the SQL statement.
Valid arguments for mandating explicit join notation are to both reduce code
errors and enforce discipline when accessing data models. However, readability is
important too.
We leave you to decide which approach suits your requirements best.
Introspection Calls
In Snowflake, an introspection call is a SQL statement used to interrogate the account
usage store or information schema of a particular database to identify metadata for
objects, columns, and their attributes. Since Snowflake is typically operated in a highly
controlled, secure manner and one significant use case is data warehousing, there is an
ChaptEr 1
tuning thE SnowFlakE Data ClouD