About Table Joins

In a SQL query, you can join multiple tables together. In effect, you make one large virtual table to SELECT rows from.

How a Join Works

Whenever you specify multiple tables in a SQL query, a join is implied. If you don't specify any other selection criteria, the result is the cartesian product of the rows in the individual tables. So if table Alpha has rows A and B, while table Beta has rows 1 and 2, then the query:

SELECT * FROM alpha,beta

returns 4 rows:

A + 1

A + 2

B + 1

B + 2

In general then, a table join will return N*M rows, where N & M are the number of rows in each table, respectively.

Of course, you're rarely interested in all possible combinations of all rows. What you're really interested in are the rows where one of the columns in table Alpha matches one of the columns in table Beta. And in general, the matching data you're looking for will be specified by a Lookup relationship.

To make it more concrete, consider the Sample Order Processing System:

• The Orders object has a Lookup to Customers
• That relationship is created by a field in the Orders object, related_to_Customer that contains the record ID of a Customer record.
• A SQL Join returns the product of all records in both tables:

Order 1 for Customer A + Customer A

Order 1 for Customer A + Customer B

Order 1 for Customer A + Customer C

Order 2 for Customer A + Customer A

Order 2 for Customer A + Customer B

Order 2 for Customer A + Customer C

...

Order 1 for Customer B + Customer A

Order 1 for Customer B + Customer B

Order 1 for Customer B + Customer C

etc.

• The records we're going to care about (highlighted above) are the ones where the related_to_Customer field in the Orders record matches the record ID of a Customer record.
• That relationship is Order.related_to_customer = Customer.id

The only remaining refinement to that concept is that, when specifying multiple tables in a SQL query, table aliases are required, and you use those alias to specify fields. So a full query can look something like this:

SELECT c.customer_name, o.date_created

FROM Customer AS c, `Order` AS o

WHERE o.related_to_Customer = c.id

Note:
The table named "Order" needs to be enclosed in backticks, because "ORDER" is a reserved word in SQL. So it is specified as: `Order`. (If used for a field name directly, the syntax would be: `Order.fieldName`.)

INNER JOIN

An INNER Join is the default. It returns only those rows that match the ON condition. So these two queries produce the same results as the previous example:

SELECT c.customer_name, o.date_created

FROM Customer AS c INNER JOIN `Order` AS o

ON o.related_to_Customer = c.id

SELECT c.customer_name, o.date_created

FROM Customer AS c JOIN `Order` AS o

ON o.related_to_Customer = c.id

Here, the row-matching criteria is specified in the ON clause. (When the tables are in a comma separated list, the WHERE clause works, but when JOIN is specifed, the ON clause is required.)

LEFT and RIGHT Joins

Sometimes you want to include rows that don't have a matching value. To do that, you use a LEFT or RIGHT join.

For example, suppose you want a list of customers and the orders they made last month. A simple query like the one shown above tells you which customers made orders, but it doesn't tell you which ones didn't make an order. Of course, you could issue a separate query to get that information, but you can also get all of the information in a single list, by issuing a LEFT or RIGHT join.

So for a query of Customers AS c, Orders AS o:

• A LEFT join includes all rows in the Customers table (the table on the left) that don't have a matching row on the right.
• A RIGHT join includes all rows in the Orders table (the table on the right) that don't have a matching row on the left.

For example:

SELECT c.customer_name, o.date_created

FROM Customer AS c LEFT JOIN `Order` AS o

ON o.related_to_Customer = c.id