page40
20
this optional clause is used to return a subset of the returned data set.
Using
LIMIT
/
OFFSET
can be useful while testing code but generally is not used
in production code; the local code comments will no doubt self-document to
explain “why.”
SQL Joins
You can join tables within the
WHERE
clause by declaring a relationship between
attributes in both tables, preferably by using primary keys and foreign keys but
alternatively by using natural keys too. The syntax for expressing join conditions comes
in two forms, discussed next.
Explicit Join Notation
Explicit join notation is considered a best practice and should be considered for
inclusion within SQL coding standards. As the name suggests, the join type for each
object accessed is explicitly stated.
You can find further information on SQL at
https://en.wikipedia.org/wiki/
Join_(SQL)
.
Here’s an example query using explicit join notation:
SELECT count(1)
FROM partsupp_baseline ps
INNER JOIN part_baseline p
ON ps.ps_partkey = p.p_partkey
INNER JOIN supplier_baseline s
ON ps.ps_suppkey = s.s_suppkey;
Implicit Join Notation
Considered by many to no longer be a best practice, this is my preference and widely
used throughout both this book and my previous books.
I have found implicit join notation to offer these advantages over explicit join
notation:
ChaptEr 1
tuning thE SnowFlakE Data ClouD