he cornerstone of the relational database system is its multiple tables and the ability to associate those tables with one another so you can combine data from different but related tables. Most of the time these associations are intuitive and work just as you expect. However, problems can arise when one of these associations returns unexpected results. In other words, the records you get, aren't the records you know you should be seeing.

Fortunately, you can dynamically relate different tables by applying what's known as a join. Technically, a join is the operation used when selecting data to create these relationships at retrieval time. What that means to you is that a join determines which records are selected and which aren't. In this article, we'll introduce you to several types of joins supported by SQL and show you how to correctly apply them to get the data you need.

SQL Standards
Technically, a join is an operation (it may be easier to think of this operation as a rule) that defines a relationship to determine which records are retrieved or acted upon. What that means is that a join is conditional—similar to a WHERE clause or criteria expression—in that the join specifies which records (rows) are selected in both tables. Certainly, the type of join significantly impacts which records are retrieved or acted upon.

Before we get started, we should clarify that this article is about SQL joins, not about the way in which particular systems support SQL joins. Although, we will discuss actual examples, for syntax purposes, we won't delve into the many nuances of each version-specific SQL. Examples will include the most basic syntax possible for simplicity's sake. For instance, we won't show alias syntax because they're all different—those details are better left to articles that deals with version-specific SQL information.

For the most part, a join can be specified in a FROM or WHERE clause, so be careful where you specify them. Because the data engine executes the clauses in a specific sequence, placement can affect the results. Use the following guidelines to determine the best place for a particular join, although most of the time, the position won't make any difference:

• Generally, the data engine fulfills the FROM clause before applying any other clauses. Adding the join here eliminates records before they're evaluated by any other clauses. As a rule, you'll find this is the recommended method.
• Any records that make it past the FROM clause arethen evaluated by an implicit join condition specified in the WHERE clause (if any).

To specify a join in the FROM clause, use the form:

SELECT fldlist | *
FROM table1 join table2 
  [ON (condition)]

Use it where table1 and table2 represent the related tables, join is the type of join applied to the relationship, and condition specifies the columns you're comparing to restrict the query's results. Usually that condition is a simple expression that compares the primary and foreign key values in the form:

ON table1.primarykey = table2.foreignkey

At this point, its important to stress that the primarykey and foreignkey references can actually refer to any column, as long as the data types match, because you can create a temporary relationship between two non-key fields (although you probably won't do so very often). This article uses the primarykey/foreignkey terms because they help emphasis the action that is going on behind the scenes. It is much more difficult to visualize the result of joining two non-key columns. However, technically, you aren't restricted to the primary/foreign key fields, and you may need the capability some day. Just remember that a relationship based on non-key columns is temporary and exists only between the two tables involved.