The user must be familiar with the database schema in use and the corresponding constraints in order to properly join tables. Writing poorly constructed SELECT statements will not harm the database but might decrease the system performance and possibly give a false relationship representation to the users. If there are poorly constructed INSERT, UPDATE, or DELETE statements, the effect could be disastrous.
Before you see any examples, certain assumptions need to be made.
- An employee cannot be entered without a department number. This indicates that the emp table is a child of the dept table.
- Addresses do not have to be entered when creating a new employee or department. Therefore, the addresses table is optional and is a child of the emp table and a child of the dept table.
If these constraints are enforced in the database, protection would be provided when a parent row is deleted but does not delete the corresponding children.
SELECT d.name dept_name,
d.dept_no dept_number,
e.first_name || e.last_name emp_name,
e.job_title title,
e.hire_date start_date
FROM dept d,
emp e
WHERE d.dept_no = e.dept_no
ORDER BY d.name, e.last_name;
In this example, all the department names and numbers will be displayed (the parent) with all of the corresponding employees (the children) in the departments.
SELECT d.name dept_name,This example shows the addition of the optional child table, called addresses. An outer join, (+) is used so that the employee row will still be retrieved even if there is no address information available yet. The DECODEs will retrieve the box number or adrs 1 depending upon the existence of box number.
d.dept_no dept_number,
e.first_name || e.last_name emp_name,
e.job_title title,
e.hire_date start_date,
DECODE(a.box_number, NULL, a.adrs_1, a.box_number) address,
DECODE(a.adrs_2, NULL, NULL, a.adrs_2) address_2,
a.city || ', '||a.state ||' '||a.zip city_stat_zip
FROM dept d,
emp e,
addresses a
WHERE d.dept_no = e.dept_no
AND e.adrs_id = a.adrs_id (+)
ORDER BY d.name, e.last_name;
