Table Joins in SQL – Part II

How Many Comparisons Do You Need?

When using Oracle syntax for table joins, a query on data from more than two tables must contain the right number of equality operations to avoid a Cartesian product. To avoid confusion, use this simple rule: If the number of tables to be joined equals N, include at least N-1 equality conditions in the select statement so that each common column is referenced at least once.

Cartesian Products

Notice also that our where clause includes a comparison on person_id linking data in people table to that of assignment table. Without this link, the output would have included all data from people and assignment table, jumbled together in a mess called a Cartesian product. Cartesian products are big, meaningless listings of output that are nearly never what you want. They are formed when you omit a join condition in your SQL statement, which causes Oracle to join all rows in the first table to all rows in the second table.

Let's look at a simple example in which we attempt to join two tables, each with three rows, using a select statement with no where clause, resulting in output with nine rows:

Select a.col_1, b.col_2

from example_1 a, example_2 b

COL_1	COL_2
A	X
A	Y
B	X
B	Y
C	X
C	Y

You must always remember to include join conditions in join queries to avoid Cartesian products. But take note of another important fact. Although we know that where clauses can contain comparison operations other than equality, to avoid Cartesian products, you must always use equality operations in a comparison joining data from two tables. If you want to use another comparison operation, you must first join the information using an equality comparison and then perform the other comparison somewhere else in the where clause. This is why table join operations are also sometimes referred to as equijoins. Take a look at the following example that shows proper construction of a table join, where the information being joined is compared further using a non-equality operation to select specific employees

select *

from   per_all_people_f papf,

          per_all_assignments_f paaf

where papf.person_id = paaf.person_id

and papf.employee_number like 'RF%'

The above sql query will only fetch employee records whose employee number starts with prefix ‘RF’

PREVIOUS POST || NEXT POST