Joining Tables

Tables are physically joined in the FROM clause of your query. They are logically joined in the WHERE clause. Table columns that appear in the WHERE clause must have the table name listed in the FROM clause. The WHERE clause is where the tables relate one to another.

The way in which the WHERE clause is constructed greatly affects the performance of the query. A two-table join does not necessarily perform better than a 10-table join.

If there are a lot of queries that have a large number of tables joined together (more than seven tables, for example), you might need to consider denormalizing certain data elements to reduce the number of table joins. This type of denormalization might be required when user productivity or system performance has significantly decreased.

In the following example, a query is written that will list all departments with their corresponding employees and the city in which the department resides.

SELECT d.name,

      e.last_name,

      e.first_name,

      a.city

FROM emp        e,

    dept       d,

    addresses  a

WHERE d.dept_no    = e.dept_no

 AND a.adrs_id    = d.adrs_id

ORDER BY d.name,e.last_name,e.first_name;

If the employee city needed to be retrieved as well, the query could be written like the following:

SELECT d.name,

      a.city  dept_city,

      e.last_ name,

      e.first_name,

      z.city  emp_city

FROM emp        e,

    dept       d,

    addresses  a,

    addresses  z

WHERE  d.dept_no      = e.dept_no

 AND  a.adrs_id      = d.adrs_id

 AND  z.adrs_id      = e.adrs_id

ORDER BY d.name,e.last_name,e.first_name;

In this example the addresses table was joined twice, enabling the city column to be retrieved for both the department and employee. In order to clarify the output, aliases were assigned to the different city columns in the SELECT portion of the query.

The following example adds the employee manager's name to the query.

SELECT d.name,

      a.city  dept_city,

      e.last_name,

      e.first_name,

      z.city  emp_city,

      m.first_name || m.last_name manager

FROM emp          e,

    dept       d,

    addresses  a,

    addresses  z,

    emp        m

WHERE  d.dept_no      = e.dept_no

 AND  a.adrs_id      = d.adrs_id

 AND  z.adrs_id      = e.adrs_id

 AND  m.emp_id       = e.manager_id

ORDER BY    d.name,e.last_name,e.first_name;

The output from this query will cause the manager (alias) column to appear as one column even though it is made from two columns. The symbol (||) is used to concatenate columns together.