A join is an SQL clause that combines records from two tables in a database. When you perform a join, you specify one column from each table to join on. These two columns contain data that is shared across both tables. You can use multiple joins in the same SQL statement to query data from as many tables as you like.
A join can be an inner join, an outer join, or a self join. An inner join, like the INTERSECT command, includes only those rows that match on the linking columns. An outer join, like UNION, includes all rows that match as well as all rows that don't match on linking columns.
Most of the time, you'll do an inner join, though you will sometimes find it useful to do an outer join. For example, you need an outer join (like the UNION command) to get all rows in these cases:
•When joining a customer table with an orders table to list the customers who ordered something in the current month (inner join) as well as those who didn't order anything (outer join).
•When joining a budget table with an expense table to list each budget item, whether or not there was an expense for that item in the current month.
•When comparing a header (master table) on the "one" side of a one-to-many relationship against a detail (transaction table) on the "many" side to see all the rows of data, whether or not they have associated details.
In R:BASE, there are two different syntactical ways to express joins. The first, called explicit join notation, uses the keyword JOIN, whereas the second is the implicit join notation. The implicit join notation uses commas to separate the tables to be joined in the FROM clause of a SELECT statement. Thus, it always computes a cross join, which results in the number of rows in the first table multiplied by the number of rows in the second table, where a WHERE clause may apply additional filtered criteria. That filter criteria is comparable to join predicates in the explicit notation.
Example of an explicit "inner" join:
SELECT ALL FROM Product +
INNER JOIN TransDetail ON +
TransDetail.Model = Product.Model
Example of an implicit "inner" join:
SELECT ALL FROM Product t3, TransDetail t2 +
WHERE t3.Model = t2.Model
Both of the above examples will result in the same output, only the example of the explicit "inner" join will be faster.
