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.
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,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.
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;
