Joins Revisited: It's All About Relationships
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Most programmers have a pretty good concept of inner joins. But when left, right, and cross joins are thrown into the mix—and then null conditions are added—things begin to get a little hazy.
For too long, joins have been shrouded in guru-ness. This article explores joins systematically, using formulas that employ ANSI SQL syntax and the SQL Server 2000 View Design Tool.
NOTE
American National Standards Institute (ANSI) is the principal standards body responsible for the standardization of Structured Query Language (SQL).
Introduction
The heart of relational databases is the join. Knowing how and when to break large tables apart and recombine them, in either the Online Transaction Processing (OLTP) or Online Analytical Reporting (OLAP) relational methodology, means knowing how and when to join.
Identifying orphaned records and non-matching records requires intimate knowledge of—guess what?—joins. Finding missing or extra rows of data in a report? Joins.
I teach a college-level course that combines Solutions Architecture and SQL Server 2000. Most students have an idea about how inner joins work, but the concept of outer joins seems foreign to them, as does expressing the join relationship in SQL syntax. You might be a little confused, too.
Although joins can be expressed in SQL in a variety of forms, including WHERE clauses, ANSI SQL join syntax has proven the most trouble-free solution, with the most consistent results for cross-platform work.
Figure 1, showing the players in ANSI join syntax, may be useful in elucidating the join concepts you'll need to grasp. Think of this as an equation or formula: just plug in column, table, and key values.
)
Figure
1 Players in an ANSI join syntax.
Keep in mind the following notes:
Inner and outer joins contain an ON clause to associate keys.
When using keys in the ON clause, it's irrelevant which key comes first—which is on the left side of the equals sign and which is on the right side.
An inner join is always contained within an outer join. If there are no additional unmatched rows in either joining table, the outer join will contain the same rows as the inner join.
An outer join will deliver at least the rows of an inner join in its result set if no WHERE clause excludes rows.
We can create joins manually by using the SQL Query Analyzer, or we can use the extensive graphics capabilities of the View Design Tool in the Enterprise Manager. If you want to build joins from scratch, see the reference section "Join Syntax" at this end of this article.
For the rest of this article, I'll focus primarily on the View Design Tool and its capabilities of auto-generating the SQL code.