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.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

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.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



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