Understanding Joins

Data broken up across relational databases has all the advantages listed previously. It also has a disadvantage in that retrieving the data is more complex. To retrieve a list of customers with matching orders information, you might have to retrieve data from two tables. And to retrieve a list of employees with the department data, you might have to retrieve the information from three tables.

If you want to retrieve data from multiple tables (within a single operation, or with a single SELECT statement), the tables must be joined (and thus the term "joins").

NOTE

Joins are logical entities, that is, data is not actually stored in any joined format, and joins do not exist or persist. Data is joined as needed, while a SQL statement is being executed.

Basic Join Syntax

You join tables by using the SQL SELECT statement to specify all the tables to be joined. For example, the following code snippet retrieves every customer and order joined into a single resultset:

 SELECT customers.customer_name, customers.customer_id,  orders.order_id, orders.order_date FROM customers, orders 

In this example, customers and orders are both tables, and both are listed in the FROM clause. The database processes this SQL statement and returns data from both tables.

TIP

When retrieving columns from multiple tables, you need to provide the fully qualified table column name in the format table.column if any column appears in more than one table. Many developers find that using this fully qualified format prevents ambiguity, so they always use it for all SQL statements.

But how does the database engine know which rows in the orders table to relate to rows in the customers table? The answer is that it does not; the join condition must be specified, or all rows will be joined to each other. So if you had 100 customers and 200 orders, the preceding SQL statement would return 20,000 rows (100 times 200) instead of 200. This kind of output is known as a Cartesian product or a cross join, and is seldom the desired output.

NOTE

Although join syntax is, for the most part, consistent across database implementations, some subtle differences can have an impact on your SELECT statements. Refer to your database's documentation for more information.

To properly join tables, you must explicitly provide the join condition. You can choose from two primary forms of syntax for doing so:

• Most databases allow you to join data in the WHERE clause, specifying the columns in each table that must be matched.

• ANSI SQL syntax requires that you specify joins in the FROM clause using the JOIN keyword.

The following example retrieves customers and orders (the same SQL statement used previously) joined using a WHERE clause:

 SELECT customers.customer_name, customers.customer_id,  orders.order_id, orders.order_date FROM customers, orders WHERE customers.customer_id=orders.customer_id 

The condition provided in the WHERE clause instructs the database to join the tables by a common columnin this case, customer_idwhich is the primary key of table customers and a foreign key in table orders.

TIP

You can join tables by using any comparison operators (not just =), and you can use multiple comparison operators (using AND, for example) too.

The following is the ANSI SQL version of this same statement:

 SELECT customers.customer_name, customers.customer_id,  orders.order_id, orders.order_date FROM customers JOIN orders  ON (customers.customer_id=orders.customer_id) 

Using ANSI SQL syntax, you specify the join condition itself by using the ON keyword immediately after the names of the tables being joined.

NOTE

The number of tables that you can join in a single SELECT statement varies from one database to the next. SQL Server, for example, allows you to join 256 tables, whereas Oracle has no limit. Consult your database documentation for more information.

Inner Joins

The most commonly used join is the inner join (the type of join used in the previous sections). Inner joins use a join condition (including a comparison operator) to match rows from both tables using values in common columns. In other words, the join condition must match rows in both tables, and values that appear in one table but not in the other are not retrieved.

Using ANSI SQL, you can specify the join type in front of the JOIN keyword as follows:

 SELECT customers.customer_name, customers.customer_id,  orders.order_id, orders.order_date FROM customers INNER JOIN orders  ON (customers.customer_id=orders.customer_id) 

TIP

If no join type is specified, INNER is assumed by default.

NOTE

There are actually two forms of INNER JOIN. The syntax shown here (testing for equality between two tables) is known as an equi-join. Another (lesser used) form of INNER JOIN is the natural join in which repeated values are eliminated from the resultset.

NOTE

Although ANSI SQL requires that you specify joins in the FROM clause, an exception is made for inner joins. It is the only form of join that ANSI SQL allows you to specify in the WHERE clause.

Outer Joins

Outer joins join tables while including rows that do not have corresponding (related) rows in the other tables. For example, to retrieve all customers (including those with no orders), you could use an outer join. The outer join assigns NULL values to all columns that are empty (because they have no matching row).

Three forms of outer joins are supported:

• LEFT OUTER JOIN (or LEFT JOIN) retrieves all rows from the left table (the left of the JOIN clause) and only related rows from the right table.

• RIGHT OUTER JOIN (or RIGHT JOIN) retrieves all rows from the right table (the right of the JOIN clause) and only related rows from the left table.

• FULL OUTER JOIN (or FULL JOIN) retrieves all rows from both tables and assigns NULL values to the columns for all rows that do not have a match in the other table.

The following example uses the ANSI SQL syntax to retrieve all customers (including those who don't have orders):

 SELECT customers.customer_name, customers.customer_id,  orders.order_id, orders.order_date FROM customers LEFT OUTER JOIN orders  ON (customers.customer_id=orders.customer_id) 

In this example, the columns order_id and order_date contain NULL for all customers with no orders.

The following SELECT statement uses a WHERE clause join:

 SELECT customers.customer_name, customers.customer_id,  orders.order_id, orders.order_date FROM customers, orders WHERE customers.customer_id*=orders.customer_id 

Here, *= creates the join. Because the * is on the left of the equal sign, all rows from the left table are retrieved (just like the LEFT OUTER JOIN). To retrieve all rows from the right table, you could use =* instead.

TIP

The only difference between LEFT OUTER JOIN and RIGHT OUTER JOIN is the order of the tables (which table is on which side of the JOIN keyword). You can change join types by simply switching the positions of the table names within the SELECT statement.

TIP

Regardless of the join type you're using, you can filter the data being retrieved by using additional conditions (known as predicates) in the ON clause.

Self-Joins

Tables can be joined to themselves to perform filtering based on data derived from the same table. This is known as a self-join. Self-joins do not require a special join syntax, so you can use all the standard join types and syntax. What makes a join a self-join is that the same table name is used twice, and this use obviously presents a syntax difficulty because SQL does not allow names to be duplicated within a SELECT statement.

To create self-joins, you must assign aliases to tables, temporary names used in the SQL statement in lieu of the real names. You specify table aliases after the table names as follows:

 SELECT customers.customer_name FROM customers c1 INNER JOIN customers c2  ON (c1.customer_city=c2.customer_city) WHERE c1.customer_state='CA' 

In this example, the customers table is self-joined, so each occurrence of the table is given a unique alias (here, c1 and c2) in the FROM clause. Once assigned, aliases are used like any other table names.