Some believe the most important part of SQL is the ability to query data. Queries typically retrieve data by joining many tables together into useful result sets. This tutorial takes the position that visibility into the data helps those new to SQL understand how joins work. To that end, the queries use Common Tabular Expressions (CTEs) instead of tables.
Default behavior of a JOIN
without a qualifying
descriptor is not simple because it may return:
- A
CROSS JOIN
(or Cartesian Product) when there is noON
orUSING
subclause, or - An
INNER JOIN
when you use anON
orUSING
subclause.
The following query uses JOIN without a qualifier or an ON or USING subclause. It also uses two copies of the single CTE, which is more or less a derived table and the result of a subquery held in memory. This demonstrates the key …
[Read more]