Local SQL Part 5: Joins: Multi-Table Queries

Written by Larry DiGiovanni

10 December 2001

In the fifth installment of Larry's SQL series, we learn how to join tables in an SQL query!Local SQL Part 5
Joins: Multi-Table Queries
© 2001 Lawrence G. DiGiovanni
Digico, Inc.
www.digicoinc.com

Preface

Local SQL is a very powerful yet commonly underutilized tool that is provided with Paradox (as part of the Borland Database Engine). It exposes the power and flexibility of the SQL sublanguage to both Paradox developers as well as interactive end-users.

This is the fifth in a planned series of articles on using Local SQL with Paradox/BDE applications. This article introduces the subject of the SQL join, which allows multiple tables to be included in a query simultaneously.

Introduction

SQL Joins permit a query to operate on more than one table at a time, and allow the creation of a result set containing column data from more than one table, just like QBE joins.

As with joining tables in QBE, a SQL join takes two parts: First, define the tables being joined; second, describe the column relationship(s) that defines the join. In QBE, this is done with example elements. In SQL, this is done using the FROM and WHERE clauses, which we discussed in earlier articles.

As with the last article, our examples will use the DBDEMOS database, which contains the Biolife example database.

Simple (Natural) Join

Suppose we want a list of the customer names, order numbers and order totals for each order. In QBE, we could do it like so:

orders.db | OrderNo | CustNo | ItemsTotal |
          | Check   | _join1 | Check      |

customer.db | CustNo | Company |
            | _join1 | Check   |

In SQL, we could do it this way:

SELECT OrderNo, ItemsTotal, Company
  FROM orders, customer
 WHERE orders.CustNo = customer.CustNo

Let's look at this query in detail:

SELECT OrderNo, ItemsTotal, Company

This line tells SQL we want the result set to contain these columns.

FROM orders, customer

This line tells SQL that these are the tables the columns are to come from. SQL knows which columns come from which tables, since we aren't choosing any column names present in both tables.

WHERE orders.CustNo = customer.CustNo

This line defines the join condition. The FROM clause tells SQL to combine the two tables using every combination of rows from customer and orders. The WHERE clause tells SQL to only use those rows with matching CustNo columns. Without the WHERE clause, the result set is referred to as a Cartesian product.

We qualified the column names in the WHERE clause with the table names so as not to confuse SQL.

Table Aliases

Remember how above we noted that SQL knows which columns come from which tables. As you might guess, it can sometimes be risky to allow SQL to make these decisions for you. It's better to be explicit about from which table each referenced column comes. There are two ways to do this. The first way is to prefix the table name to the column reference:

SELECT OrderNo, ItemsTotal, Company
  FROM customer, orders
 WHERE customer.CustNo = orders.CustNo

This approach gets unwieldy if we have long table names, or many aliases. To streamline this, we can use table aliases. Tables aliases are created in the FROM clause, and can be used to qualify column names in any other clause. To use the above example, we could rewrite it as such:

SELECT o.OrderNo, o.ItemsTotal, c.Company
  FROM customer c, orders o
 WHERE c.CustNo = o.CustNo

This time, we've aliased orders as o and customer as c. Once you alias a table, it's best to use the alias each time a column is referenced. One other benefit to strict aliasing is that it makes the SQL easier to interpret by those who follow.

Outer Join

Outer joins are handled a little differently then above. Remember that an outer join is a join in which unmatched rows of one table are included in the result set, along with the matched rows. For example, if there are customers with no orders (as is the case with Diver's Grotto), we can still show them in our list, using an outer join.

In QBE, it would be something like:

orders.db | OrderNo | CustNo | ItemsTotal |
          | Check   | _join1 | Check      |

customer.db | CustNo  | Company |
            | _join1! | Check   |

in Local SQL, we'd do this:

SELECT o.OrderNo, o.ItemsTotal, c.Company
  FROM customer c LEFT OUTER JOIN orders o
    ON (c.CustNo = o.CustNo)

Local SQL uses the ANSI 92 syntax for outer joins. We could do a RIGHT OUTER JOIN giving the same result as shown below:

SELECT o.OrderNo, o.ItemsTotal, c.Company
  FROM orders o RIGHT OUTER JOIN customers c
    ON (c.CustNo = o.CustNo)

Bottom line, an outer join generates NULL columns for the unmatched rows in the outer joined table. The LEFT or RIGHT simply indicates which table (the one listed first or second, respectively) gets all of the rows included.

Summary

Joining tables allows a user to query multiple tables simultaneously, selecting columns from any joined tables to be included in the result set. An inner join selects only rows from both tables that meet the join criteria. An outer join selects all rows from one table, plus row meeting the join condition from the joined table.

Local SQL Part 6

< Prev		Next >

[ Back ]