Paradox Community
Search:

 Welcome |  What is Paradox |  Paradox Folk |  Paradox Solutions |
 Interactive Paradox |  Paradox Programming |  Internet/Intranet Development |
 Support Options |  Classified Ads |  Wish List |  Submissions 


Interactive Paradox Articles  |  Beyond Help Articles  |  Tips & Tricks Articles  


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


Discussion of this article


 Feedback |  Paradox Day |  Who Uses Paradox |  I Use Paradox |  Downloads 


 The information provided on this Web site is not in any way sponsored or endorsed by Corel Corporation.
 Paradox is a registered trademark of Corel Corporation.


 Modified: 15 May 2003
 Terms of Use / Legal Disclaimer


 Copyright © 2001- 2003 Paradox Community. All rights reserved. 
 Company and product names are trademarks or registered trademarks of their respective companies. 
 Authors hold the copyrights to their own works. Please contact the author of any article for details.