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.created_date

FROM Customers AS c, Orders AS o

WHERE o.related_to_customer = c.id

Note:
If the table is named "Order" rather than "Orders", the table name needs to be enclosed in backticks, since "ORDER" is a reserved word: `Order`.