RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Getting the Right Data with SQL Joins : Page 2

Associating tables in SQL is usually a breeze. But what happens when you find yourself looking at the completely wrong set of records and you don't know why? Find out how correctly applying joins can help you understand and avoid these mysterious data returns.

A Few Guidelines
There are a few general guidelines that are common to almost all joins:
  • The columns (fields) specified in fldlist can include all of the columns in or just a subset from the data source.
  • A join can be built on a column not referenced in fldlist.
  • Ambiguous columns must be fully qualified.
  • The above syntax form is based on just two related tables even though SQL can handle multiple joins in the same statement.
  • Although the Equal To (=) operator is the most common, you can use any of the following SQL comparison operators: =, >, <, >=, <=, and <>. (This isn't true of all systems, be sure to check your system's documentation for more specific information.)

Figure 1: A simple inner join displays the category name for each product.

Now, take a look at a quick example using Microsoft SQL Server's sample database, Northwind. The following statement lists each product by name and category, as shown in Figure 1:

SELECT Products.ProductName, Categories.CategoryName
FROM Products JOIN Categories
  ON Products.CategoryID = Categories.CategoryID 
(The figure doesn't show all 77 records.) In the example above the joined columns have identical names, but that isn't required. Just remember the data type must still be the same. Oracle introduced this syntax with 9i; it was not available in previous releases.

If you run the statement in Microsoft Access use INNER JOIN instead of JOIN as follows:

SELECT Products.ProductName, Categories.CategoryName
FROM Products INNER JOIN Categories
  ON Products.CategoryID = Categories.CategoryID
Jet SQL doesn't support the lone JOIN keyword. Transact-SQL (T-SQL) and Oracle SQL both interpret the lone JOIN keyword as an inner join.

Occasionally, you may want to express a join condition in the WHERE clause (although you probably won't have this need too often). Remember, when you do so, the join is implicit, but condition is expressed in the same manner using the same rules for the FROM syntax. Some systems only allow this syntax to be used with an inner join, which you'll learn about in the next section. (There are exceptions. Oracle releases prior to 9i, supported only implicit joins in the WHERE clause. Consult your system's documentation for the supported syntax.)

To express a join in the WHERE clause, use this form:

SELECT fldlist | *
FROM table1, table2
WHERE condition
where condition is a simple comparison of two columns. For instance, the previous FROM example could just as easily be written like this:

SELECT Products.ProductName, Categories.CategoryName
FROM Products, Categories
WHERE Products.CategoryID = Categories.CategoryID 
Both statements yield the same recordset, but that won't always be the case. While it is true that both statements in this example return the same results, the two syntax forms aren't interchangeable on a technical level.

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date