![]() |
![]() |
|
![]() |
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.CustNoLet's look at this query in detail: SELECT OrderNo, ItemsTotal, CompanyThis line tells SQL we want the result set to contain these columns. FROM orders, customerThis 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.CustNoThis 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.CustNoThis 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.CustNoThis 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. ![]() |
![]() |
|