Databases Reference
In-Depth Information
the company, he needs to see the department name in the report. That informa-
tion is in the
DEPARTMENTS
table. Janice will join the two tables on the common
column,
DEPARTMENT_ID
, and produce a report that is much more readable:
select employee_id "Emp ID", last_name || ', ' ||
first_name "Name", department_name "Dept"
from employees e, departments d
where e.department_id = d.department_id;
Emp ID Name Dept
---------- ------------------------- --------------------
100 King, Steven Executive
101 Kochhar, Neena Executive
102 De Haan, Lex Executive
103 Hunold, Alexander IT
104 Ernst, Janice IT
105 Austin, David IT
...
201 Hartstein, Michael Marketing
202 Fay, Pat Marketing
203 Mavris, Susan Human Resources
204 Baer, Hermann Public Relations
205 Higgins, Shelley Accounting
206 Gietz, William Accounting
106 rows selected.
Notice that table aliases are used. You've already seen quite a few column
aliases in previous examples, and tables can be aliased also, either for clarity or
for performance reasons. In this case, the aliases are necessary to identify which
columns in which table are to be compared in this query. Typically, the column
names match, but that is not a requirement for columns that are matched in a
clause.
King tells Janice that the report looks good, but he also wants to see the full
job description for each employee. Janice adds another table to the query and
expands the
WHERE
WHERE
clause. She also adds an
ORDER BY
clause to ensure that the
report stays in employee ID order:
select employee_id "Emp ID",
last_name "Name", department_name "Dept",
job_title "Job"
from employees e, departments d, jobs j
where e.department_id = d.department_id
