17 Inner versus Outer Joins

• ·         Performs a join on two tables, retrieves all the rows in the Left table, even if there is no match in the Right table. It also retrieves all the rows in the Right table, even if there is no match in the Left table. FULL OUTER JOIN
• ·         A join that returns the unmatched rows as well as matched rows- outer join
• ·         Performs a join on two tables, retrieves all the rows in the Left table even if there is no match in the Right table. -LEFT OUTER JOIN
• ·         Performs a join on two tables, retrieves all the rows in the Right table even if there is no match in the Left table. –RIGHT  OUTER JOIN
• ·         A join of two or more tables that returns only matched rows-INNER JOIN

Use the Oracle database for problems 1-7.

1.   Return the first name, last name, and department name for all employees including those employees not assigned to a department.

SELECT  emp.first_name "First Name", emp.last_name "Last Name" , dpt.department_name  "Department Name"

FROM  employees emp LEFT OUTER JOIN departments dpt ON emp.department_id = dpt.department_id;

2.  Return the first name, last name, and department name for all employees including those departments that do not have an employee assigned to them.

After reading next problem, I assume, that problem, says to miss an employee, if it doesn’t have a department assigned.

SELECT  emp.first_name "First Name", emp.last_name "Last Name" , dpt.department_name  "Department Name"

FROM  employees emp RIGHT OUTER JOIN departments dpt ON emp.department_id = dpt.department_id;

OR

SELECT  emp.first_name "First Name", emp.last_name "Last Name" , dpt.department_name  "Department Name"

FROM  departments dpt  LEFT OUTER JOIN employees emp ON  dpt.department_id = emp.department_id;

3.  Return the first name, last name, and department name for all employees including those departments that do not have an employee assigned to them and those employees not assigned to a department.

SELECT  emp.first_name "First Name", emp.last_name "Last Name" , dpt.department_name  "Department Name"

FROM  employees emp FULL OUTER JOIN departments dpt ON emp.department_id = dpt.department_id;

4.  Create a query of the DJs on Demand database to return the first name, last name, event date, and description of the event the client held. Include all the clients even if they have not had an event scheduled.

SELECT ct.first_name, ct.last_name, ev.event_date, ev.description

FROM  d_clients ct LEFT OUTER JOIN d_events ev ON ct.client_number = ev.client_number;

5.  Using the Global Fast Foods database, show the shift description and shift assignment date even if there is no date assigned for each shift description.

SELECT f_shifts.description "shift description", f_shift_assignments.shift_assign_date AS "shift assignment date"

FROM  f_shifts LEFT OUTER JOIN f_shift_assignments ON f_shifts.code = f_shift_assignments.code;