Select-SQL

SELECT-SQL is one of our favorite commands. It lets you create an output set from one or more tables based on various criteria and send it to one of several locations. What makes SELECT so cool is that you specify what you want rather than how to get it—FoxPro figures out how to get it internally.

A SELECT-SQL statement is often called a query because you’re querying the database.

Usage

SELECT [ ALL | DISTINCT ]
       [ TOP nHowMany [ PERCENT ] ]
        eColumn1 [ AS ColumnName1 ]
             [, eColumn2 [ AS ColumnName2 ] ... ]
       FROM [ FORCE ]
            [ Database1! ]Table1 [ [ AS ] LocalAlias1 ]
              [ [ INNER | LEFT [ OUTER ] | RIGHT [ OUTER ]
               | FULL [ OUTER ] ] JOIN
               [ Database2! ]Table2 [ [ AS ] LocalAlias2 ]
                 [ ... ]
               [ ON lJoinCondition1 ]
               | , [ Database3! ]Table3 [ [ AS ] LocalAlias3 ]
                 [ ... ] ]
        [ WHERE lConditions ]
        [ GROUP BY GroupColumn1 [, GroupColumn2 ... ] ]
        [ HAVING lGroupFilter ]
        [ UNION [ ALL | DISTINCT ] SELECT ... ]
        [ ORDER BY OrderCriteria1 [ ASC | DESC ]
              [, OrderCriteria2 [ ASC | DESC ] ... ] ]
        [INTO CURSOR CursorName [ NOFILTER ] [ READWRITE ]
         | INTO TABLE | DBF TableName
         | INTO ARRAY ArrayName
         | TO FILE FileName [ ADDITIVE ]
         | TO PRINTER [ PROMPT ]
         | TO SCREEN ]
       [ PREFERENCE PreferenceName ]
       [ NOCONSOLE ]
       [ PLAIN ]
       [ NOWAIT ]

Frankly, we don’t think a syntax diagram is a very good way to learn SELECT-SQL. It’s just too complex for that. One good way is to play with the Query Designer, though it can’t handle all the queries you can write by hand. (In particular, it has trouble with certain kinds of joins.) Open the SQL window, so you can see the generated statements, then try different choices and see what happens. Once you’re comfortable with the basic clauses and options, you can move on to writing your own queries and try out the more complicated stuff.

Nonetheless, let’s try to make sense of this whole-language-in-one command. Like the other SQL commands, SELECT is non-procedural, which means you specify what you want, but you don’t have to tell FoxPro how to get it. SELECT processes one or more tables, producing what’s called a result set. In FoxPro, you have many alternatives of what to do with the result set. The default (from FoxPro 2.5 on) is to store it in a cursor and BROWSE the cursor. The PREFERENCE and NOWAIT options listed above apply only in that case and behave as they do for BROWSE.

Fielding a Team

There are two clauses required in every query: the field list, specified immediately after the SELECT, and the table list, which goes in the FROM clause. The field list contains a list of expressions, which create the result set. The simplest version is to put just “*”, which means “include every field from every listed table.” Otherwise, each expression can be as simple as a single field or can be a complex computation. You can mix and match the “*” with a field list, including all fields from some tables with selected fields from others, just by using an alias in front of the “*” to specify the table.

There are a few ground rules. While you can call both built-in functions and UDFs in field expressions, you need to be careful about them. Check out Help for some fair warnings on this. Because SELECT is non-procedural, you can’t count on things happening in a particular order. Don’t expect to find the record pointer in a particular location, but do be sure to put it back if you move it.

The first record the query finds (which could be almost any) defines the type and size for each field in the result set. So, it’s important for that record to have the appropriate shape for the results you want to generate. For example, you might use an expression like:

IIF(SomeField, SomeOtherField, "X")

The problem is what happens if SomeField is .F. for the first record generated. The result is created with a one-character field rather than one the width of SomeOtherField. A better expression is:

IIF(SomeField, SomeOtherField, PADR("X", LEN(SomeOtherField)))

In the case of numeric fields, use a “picture” to show the size and decimals of the number. For example, the following code forces FoxPro to create a 9-byte numeric field with two decimal places:

IIF(SomeField, SomeOtherField, 000000.00)

The AS clause lets you assign a field name of your choice (rather than FoxPro’s choice) to a field of the result set. FoxPro’s choice depends on the expression used for the field. If it’s just a field name, the resulting column has the same name (unless multiple columns of the result originate from same-named fields). If it’s an expression, FoxPro chooses a name like EXP_1 for a simple expression or function_EXP_1 when one of the built-in functions is used (like SUM_EXP_1). Frequently, the field names in the result set are unimportant and you can just let FoxPro do its thing. But, at other times, you need to know the name of the resulting field—in those cases, AS is worth its weight in gold (of course, “AS” wouldn’t be that heavy in gold, so maybe we’re damning with faint praise here).

You come FROM where?

The second required clause is a list of tables used in the query. In Visual FoxPro, table names can be qualified by the containing database, like TasTrade!Customer. This lets you use same-named tables from multiple databases in a query. For example, you might compare customer lists from two different databases. It also lets you use tables without having to explicitly open the database first.

The FROM clause also lets you assign a local alias to a table. The local alias is used only within the query. The AS keyword for local aliases is optional; we tend to omit it.

We’ve found two good uses for local aliases. The first is when we need to use multiple tables of the same name or to use the same table more than once in a query (the latter is called a self-join, by the way). In this case, the local alias is pretty much required. The second case is when the name of a table is long—sometimes, we assign a shorter local alias just to cut down on typing. One warning here: Single-letter local aliases can sometimes confuse FoxPro because the single letters from A through J also refer to the first 10 work areas. We recommend you stick to aliases of two letters or more.

The optional FORCE clause is for times when you know better than VFP what order the tables should be joined in for optimal results. When you include FORCE, the joins are performed in the order specified in the FROM clause without any attempt to optimize. See “Faster Than a Speeding Bullet” in “Franz and Other Lists” for more query optimization.

May I JOIN You?

When a query contains more than one table, you have to (well, you don’t have to, but you’ll be sorry if you don’t) provide a way for FoxPro to match up records in the tables. SELECT’s default behavior is to pair every record in each table with every record in every other table. That is, by default, the number of records in the result is the product of the number of records in each table. For example, if you have 500 customers in one table and 2000 orders in another, by default, the result set of a query listing customers and orders in the FROM clause would contain 500*2000 = 1,000,000 records.

You’ll rarely want this default result (called a Cartesian join), of course. Normally, when you include two (or more) tables in a query, there’s some data in the tables that determines which records match up. In the Customers and Orders example, both tables probably include a Customer ID field and you only want to match each customer record to the orders for that customer. That’s a join condition. It tells how to join two tables so that the results are meaningful. Normally, for a query involving N tables, you have at least N-1 join conditions.

Most often, the join conditions for two tables are the same as the persistent relation between them, but not always. Some of the most interesting queries we’ve written involve creative and complex join conditions. For example, a self-join might pair a daily sales record with the corresponding record from the previous year.

There are two ways to specify join conditions. All versions of FoxPro (back to 2.0, in which SELECT was introduced) allow you to put join conditions in the WHERE clause discussed below. Starting in VFP 5, however, joins have their own place, as well, as part of the FROM clause. You do have to do it one way or the other, though. Either all your joins go in the FROM clause or they all go in the WHERE—there’s no mix-and-match permitted.

When joins are specified in the FROM clause, the join condition follows the ON keyword; the actual expression you put there is no different than what you’d put in the WHERE clause.

When you put your joins in the FROM clause, however, you have some options you don’t get when they’re in the WHERE clause. Joins fall into two types—inner and outer—and outer joins can be further divided into left, right and full. (Funny, you’ll find all those types as keywords in the syntax diagram.) In an inner join, only those records in one table that match up to a record in the other table are included in the result. For example, if you use an inner join for customers and orders, the result set includes only those customers who have actually placed an order. The WHERE clause can handle only inner joins. Developers working in VFP 3 and earlier have to create an outer join by using the UNION keyword to join two or more SELECT statements to gather those records that match and those that don’t. The new JOIN syntax is a welcome relief from that challenge.

Outer joins let you deal with unmatched records. In a left outer join, all the records from the table on the left (that is, before the JOIN keyword) are included along with whichever records from the table on the right match up. If a record on the left has no match on the right, any fields in the result that are based on the table on the right are given a null value. A left outer join of Customers and Orders (in that order) includes all customers and their corresponding orders; customers who’ve placed no orders show up, but any fields related to orders are null.

A right outer join is the same thing in reverse. All the records from the table on the right (that is, after the JOIN keyword) are included, along with any matching records on the right.

A full outer join includes all records from both tables, matched appropriately.

If queries were limited to two tables, we’d be done. However, you can put as many tables as you want in a query (subject to work area, command length and file handle limitations). So we need a way to specify joins involving three or more tables. In fact, any given join actually joins the result so far (which is a cursor) to a single table. Logically, we join multiple tables by doing a series of individual joins, each one adding another table to the results so far.

There are two ways to specify that series of joins, however, and to make matters more complicated, a single query can include both approaches.

The first way (which is the only one the Query Designer knows how to do) is to nest the joins. You list all the tables separated by their appropriate JOIN keywords and follow them with the ON clauses in reverse order. That is, the first ON clause matches up to the last JOIN listed. We call this the “nested syntax” and we tend to indent it to show the nesting. The nested syntax is great for hierarchical relationships, like Customer to Orders to Order_Line_Items to Products, where the FROM clause might look like:

FROM Customer ;
   JOIN Orders ;
      JOIN Order_Line_Items ;
         JOIN Products ;
           ON Order_Line_Items.Product_Id = Products.Product_Id ;
        ON Orders.Order_Id = Order_Line_Items.Order_Id ;
     ON Customer.Customer_Id = Orders.Customer_Id

But not all the relationships you want to deal with in a query are hierarchical. Suppose you want to join an order to the customer who placed the order, the employee who took the order and the shipper used to send the order. While the order table serves as a parent to the customer, employee and shipper tables, there’s no relationship among the three child tables. (We call these “unrelated siblings,” and it mirrors the way we occasionally feel about our own siblings.)

While you can use the nested syntax for unrelated siblings, the resulting query is hard to read and hard to maintain. We prefer to use the less documented, but more readable, “sequential syntax.” In this form, each JOIN is immediately followed by its corresponding ON clause. You can put as many of these in a row as you want and they’re performed in the order listed. (Actually, joins may be performed in weird orders for optimization reasons, but from a logical perspective, sequential joins happen from top to bottom.) The join between Orders, Customer, Employee and Shippers might look like this:

FROM Orders ;
   JOIN Customer ;
     ON Orders.Customer_Id = Customer.Customer_Id ;
   JOIN Employee ;
     ON Orders.Employee_Id = Employee.Employee_Id ;
   JOIN Shippers ;
     ON Orders.Shipper_Id = Shippers.Shipper_Id

Outer joins are a little trickier to manage than inner joins. Once you’ve performed an outer join, you need to remember that you’ve done so and ensure that subsequent joins carry along the extra results. In many cases, that turns out to mean that each join that logically follows an outer join also needs to be an outer join.

WHERE, Oh, Where Can My Data Be?

Once you match up corresponding records, the next thing is to include only those records with relevant data. Filter conditions do this job—they live in the WHERE clause. This is where you specify things like “only orders this year” or “only customers in Pennsylvania.” A query may have no filter conditions or many, complex filter conditions.

Both join conditions and filter conditions are Rushmore optimizable. See the section “Faster Than a Speeding Bullet” in “Franz and Other Lists” for clues on speeding up your queries.

The WHERE clause can use wildcards to match strings using the LIKE operator (which is similar to FoxPro’s LIKE() function). For some reason, the wildcard characters used here are “%” for zero or more characters and “_” for exactly one character. So to match any string containing the letter “j”, your WHERE clause might look like:

WHERE cMyField LIKE "%j%"

There are optimization consequences to using the LIKE operator and wildcards. In our tests, simply including LIKE changes from full to partial optimization for an indexed field. Using a wildcard at the beginning of the string results in no optimization for that condition. This all makes sense to us, based on our understanding of how Rushmore works.

Since you might occasionally need to perform a wildcard search where one of the characters you’re searching for is “%” or “_”, there’s also a special ESCAPE keyword that lets you specify a character to place before the wildcard character to indicate that this use of it is not a wildcard. For example, to search cMyField for strings beginning with “_” and ending with “_”, but with any characters in between, you could write:

WHERE cMyField LIKE "\_%\_" ESCAPE "\"

This clause says that the escape character is “\”, so any wildcard character preceded by “\” should be treated as a regular character.

The Help file includes a number of examples involving wildcards and the escape character.

WHERE has two more comparison operators not found elsewhere in FoxPro: IN and BETWEEN. IN checks whether the specified field or expression is contained in a given list. (IN is a lot like FoxPro’s INLIST() function.) It’s handy for checking things like whether the customer’s state is one of some subset, like (“PA”, “NJ”, “DE”). BETWEEN checks whether the field or expression is in the specified range—it works pretty much like FoxPro’s BETWEEN() function, and they seem to be equally fast. Both BETWEEN and BETWEEN() are inclusive, meaning that matches to the specified boundaries are included in the result. Like FoxPro’s BETWEEN(), the BETWEEN clause does require proper placement of the lower and upper boundaries—it won’t work if you reverse them.

All three of these special operators (LIKE, IN and BETWEEN) can be preceded by NOT to choose the records that don’t match the specified condition.

The WHERE clause also may contain sub-queries, which are simply queries within another query. The sub-query is executed and then some comparison is made to its results. SELECT has several special operators for performing these comparisons: IN, EXISTS, ALL, and ANY or SOME. Of these, we’ve only found a real need for IN, which lets you see whether a particular value occurs in the results of a sub-query. We’re actually far more likely to use NOT IN to find records that don’t have a match in a sub-query. This is the best way to find the differences between two tables—all the records contained in one, but not in the other.

All the clauses discussed above relate to original data. The next few clauses work on the intermediate results obtained by joining the records, applying the WHERE clause and computing the expressions in the field list, or on even later results.

Just One of the GROUP

The GROUP BY clause lets you consolidate groups of records into a single result. For example, you might combine all the orders for each customer into a single customer order summary. Or you might count the number of customers by country. There’s a trap here for the unwary—the term “group” in SELECT has a different meaning than “group” in a report. (In a report, grouping simply refers to layout; it doesn’t consolidate.)

When you include GROUP BY in a query, all records whose values exactly match in all fields listed in the GROUP BY clause are consolidated into a single record.

Normally, you use GROUP BY together with a set of functions built into the SELECT command: COUNT(), SUM(), AVG(), MAX() and MIN(). These compute the specified function for the records in a group. You can put either a field name or a more complex expression inside the field. We refer to these as aggregate functions because they compute aggregate results.

All the aggregate functions operate correctly by ignoring null values. So, AVG(SomeField) is really the average of the non-null values of SomeField. This is a welcome change from older versions of FoxPro, which didn’t recognize nulls, and brings FoxPro into line with other languages that speak SQL.

The biggest beneficiary of this change may be COUNT(), which accepts “*” as its parameter to give you the number of records in the group. In older versions of FoxPro, it didn’t matter whether you put “*” or the name of a field inside COUNT()—the results were the same. In VFP 3 and later, COUNT(SomeField) tells you the number of records in the group with a non-null value for SomeField, while COUNT(*) still gives you the number of records in the group.

If you use one of the aggregate functions without a GROUP BY clause, the result set contains a single record. It’s as if you specified a grouping expression that put all the records in a single group.

Once you’ve done the grouping, you may want to omit some of the groups. The HAVING clause does that for you. It gives you another chance to filter the results, this time looking at intermediate data rather than original data. HAVING accepts the special LIKE, IN and BETWEEN operators, but doesn’t accept sub-queries.

Never use HAVING without GROUP BY. If you’re not grouping results, you should be able to move the conditions to the WHERE clause, instead. Since WHERE is Rushmore-optimizable and HAVING is not, this can make an enormously significant speed difference. Actually, we have heard of a very few cases where you’d use HAVING without GROUP BY, though we’ve never run into one ourselves.

By ORDER of the King

When you have the results you want, whether grouped or not, you might want to put them in a particular order. It happens that, in some cases (such as UNIONed or GROUPed queries or single-table queries where the original is ordered appropriately), results come out in order anyway. Don’t count on this, though; it’s an artifact of the way FoxPro performs the query and might change in future versions or perhaps even under unusual conditions in the current version.

Instead, when you need to be sure that query results are in a particular order, use the ORDER BY clause. ORDER BY says to sort the results (after filtering, grouping and filtering again) based on the fields listed. Records are sorted on the first field listed. Then, those that match in the first field are sorted on the second field listed. Then, those that match in the first and second fields are sorted on the third, and so on.

Interestingly, any field from any table in the FROM clause can be used here. However, you cannot put an expression in this clause. So, if you want to sort on a result field that’s based on an expression, you have to either rename that field with AS (a pretty good idea, anyway) or specify it by its numeric position in the result set.

Each field in the ORDER BY clause can be applied in either ascending or descending order. So, you could show Customer and Orders in alphabetical order by customer, then from most recent to oldest (descending date) order within each customer.

Ordering can be pretty slow, especially if the result set is large. In some cases, it may be better to omit ORDER BY from the query and create an index afterwards. (You can create one index tag against a read-only cursor created by SELECT.) With arrays, it’s almost always better to apply ASORT() after the query—the exception is when you need to sort on multiple fields, because ASORT() can’t do that.

Once you’ve put the results in order, you can eliminate some of them. The TOP clause, added in VFP 5, says to include the first nHowMany records or first nHowMany percent of the records in the final result. The records are selected based on the order specified.

While the TOP clause is handy occasionally, it’s not really that useful for two reasons. First, it applies to the overall result set—you can’t choose the first nHowMany records in each group. So you can’t, for example, return the five most recent orders for each customer. Second, it isn’t applied until the whole result set has been accumulated, so it doesn’t make things any faster.

Let’s Not Get INTO That

In any case, after all this, you’ve got a nice result set. Now where do you put it? FoxPro gives you a whole bunch of choices. One group of choices is more Xbase-ish while the other is SQL-like.

The Xbase group uses the TO clause, which is almost like the TO clause of the various Xbase commands. (DISPLAY and LIST come to mind.) You can choose to send the results to a file, to the printer or to the screen. The TO SCREEN option is the one that makes this different from the usual Xbase TO—it’s needed for two reasons. First, the default for SELECT, unlike Xbase commands, is to put results in a cursor and BROWSE the cursor. The second, important reason for the TO SCREEN option is that putting the results directly on the screen was the default in FoxPro 2.0, and there’s always the off-chance that somebody’s application depends on this ability.

The NOCONSOLE and PLAIN options apply only when you’re using TO and behave as they do elsewhere. NOCONSOLE keeps the output from appearing in the active window. PLAIN omits column headings.

We use the TO options only for interactive, quick-and-dirty work, never in applications. For example, naturally we used a table to track the progress of this book. SELECT … TO FILE gave us a quick way to get a list of all sections ready for some processing—for example, to go to our technical editor.

Outside that kind of situation, we can’t imagine using SELECT … TO any more than we’d use DISPLAY or LIST in an application.

So what do we do with query results? The SQL INTO clause gives us three options: Save them in a cursor, save them in a table, or save them in an array. The array bit is actually a FoxPro extension not usually available in SQL. We’re really glad it’s there, though—we use it a lot. SELECT … INTO ARRAY is really handy for populating things like listboxes and comboboxes.

The difference between INTO CURSOR and INTO TABLE is that the cursor goes away when we’re done with it, while the table sticks around. Each has its uses.

Before VFP 7, cursors created by SELECT-SQL were always read-only. You couldn’t change the data in them either interactively or programmatically. In VFP 7, the new READWRITE clause indicates that the cursor created should be (surprise) read-write.

In FoxPro 2.x, read-only cursors were an important, frustrating limitation. In Visual FoxPro, it didn’t matter too much. If you needed to be able to update cursor contents, you could use a view instead of a query to create the cursor.

There’s actually a way around this limit, even in FoxPro 2.x. As long as the query creates a “real” cursor and not just a filter of the original table, you can USE the cursor AGAIN in another work area and it’s read-write there.)

VFP 5 added the NOFILTER clause for INTO CURSOR. In order to do things as fast as it can, FoxPro pulls a little trick. If a query involves only a single table, has no calculated fields, is fully optimizable, and is sent to a cursor, rather than going to the trouble of creating a whole new cursor, FoxPro simply filters the original table. Often, that’s good enough for whatever you have in mind. However, in some situations, especially those where the query result is then used in a subsequent query or where you want to index the cursor, having only a filtered view of the original causes problems. In VFP 3 and earlier versions, you worked around this by putting something in the query that outwitted FoxPro. Starting in VFP 5, it’s a lot easier—just add NOFILTER to the query and FoxPro always creates a real cursor. (Doesn’t “real cursor” sound like an oxymoron?)

Although you can include both READWRITE and NOFILTER in a query, NOFILTER is unnecessary. By definition, any read-write cursor is not just a filter of the original table.

Odds and Ends

The ALL and DISTINCT keywords determine whether the result set contains every record found or only a unique set of records. When you specify DISTINCT, every field is compared; records that exactly match another record in the set are eliminated, so that each unique combination appears only once. If this sounds slow, it’s because it is slow. Since you rarely want to match up every single field, you’re usually better off culling duplicates with GROUP BY.

DISTINCT does have another use. You can put it inside the aggregate functions so they only compute whatever against unique values of the field. For example, you can count the number of unique hire dates among the TasTrade employees by including COUNT(DISTINCT Hire_Date).

If you’ve been following along, you’ll notice we’ve mentioned every clause listed above except UNION. This clause lets you put the results of several queries into a single result set. It’s kind of like a built-in APPEND command. Each query is executed, then all the results are stuck together in a single cursor or table or array (or output together if you insist on using the TO clause).

There’s one important set of rules for UNIONing queries. The field list for each query in the UNION must have the same number of items. Corresponding items must be the same type. The first query in the UNION creates the template for the result, so fields in subsequent queries must be no larger than those in the first query. (They can be smaller.) If you think about what’s going on here, the whole set of rules makes sense.

By default, a UNION includes only DISTINCT records—just like including the DISTINCT clause in a single query, UNION automatically culls out exact duplicates. There are two reasons this can be a problem. First, UNION doesn’t bother to check whether the duplicates originated in the same table—they’re still eliminated. Second, culling duplicates this way is slow. Our recommendation is to use UNION ALL except when you know you want duplicates removed.

There’s a limit of nine UNIONs in a single query. That is, you can’t combine more than 10 individual queries at once. We’ve never considered this a problem for two reasons. First, we’ve never needed to combine more than three or four queries at once. Second, you can simply break a larger problem into smaller parts. If you should need to combine the results of 20 queries (we can’t imagine why), do two 10-table UNIONs, placing each in a cursor. Then, UNION those two results.

Because ORDER BY and TO/INTO are post-processing clauses, a UNIONed query should contain only one of each of these. The other clauses can be applied separately to each query in the UNION.

In VFP 3 and earlier, UNION was often used to simulate outer joins.

Several old bugs were fixed in the service packs to VFP 6 and in VFP 7, so that SELECT's results are more accurate. If you have a query or view whose results have changed between versions, check out the ReadMe file for VFP 7.

Example

* Compute the number of 1994 orders for each customer
* in the TasTrade customer list, but keep only those with
* 10 or more. Note that you can't use the TOP clause
* for this.
SELECT Customer.Customer_ID, COUNT(*) ;
   FROM TasTrade!Customer ;
     JOIN TasTrade!Orders ;
       ON Customer.Customer_ID=Orders.Customer_ID ;
   WHERE YEAR(Orders.Order_Date)=1994 ;
   GROUP BY 1 ;
   HAVING COUNT(*)>=10 ;
   INTO CURSOR OrderCount

* Find all the customers with no orders.
SELECT * FROM Customer ;
  WHERE Customer_ID NOT IN (SELECT Customer_ID FROM Orders)

* Join Orders with Customer, Employee and Shipper.
* Here's the nested syntax - does the order of the joins
* make sense to you? It doesn't to us.
SELECT Customer.company_name, ;
       Orders.order_date, ;
       Employee.last_name,;
       Shippers.company_name AS Shipper_Name;
 FROM tastrade!customer ;
   JOIN tastrade!employee;
     JOIN tastrade!shippers ;
       JOIN tastrade!orders ;
       ON Shippers.shipper_id = Orders.shipper_id ;
     ON Employee.employee_id = Orders.employee_id ;
   ON Customer.customer_id = Orders.customer_id

* Here's the same query using the sequential syntax.
* We think it's a lot more readable.
SELECT Customer.company_name, ;
       Orders.order_date, ;
       Employee.last_name,;
       Shippers.company_name AS Shipper_Name ;
 FROM tastrade!orders ;
   JOIN tastrade!customer ;
     ON Customer.customer_id = Orders.customer_id ;
   JOIN tastrade!employee  ;
     ON Employee.employee_id = Orders.employee_id ;
   JOIN tastrade!shippers ;
     ON Shippers.shipper_id = Orders.shipper_id

* Here's an example where the nested syntax makes sense
* because the tables have a hierarchical relationship.
SELECT Company_Name, Order_Date, Product_Name ;
 FROM Customer ;
   JOIN Orders ;
     JOIN Order_Line_Items ;
       JOIN Products ;
         ON Order_Line_Items.Product_Id = Products.Product_Id ;
     ON Orders.Order_Id = Order_Line_Items.Order_Id ;
   ON Customer.Customer_Id = Orders.Customer_Id
* What if we want to see all the customers in that query?
* We need an outer join between Customer and Orders.
SELECT Company_Name, Order_Date, Product_Name ;
 FROM Customer ;
   LEFT JOIN Orders ;
     JOIN Order_Line_Items ;
       JOIN Products ;
         ON Order_Line_Items.Product_Id = Products.Product_Id ;
       ON Orders.Order_Id = Order_Line_Items.Order_Id ;
     ON Customer.Customer_Id = Orders.Customer_Id

See Also

ASort(), Between(), Browse, Create Cursor, Create SQL View, InList(), Like(), Set ANSI