Oracle SQL Fundamentals/Joining Data

This lesson introduces joining data.

Objectives and Skills

Objectives and skills for the joining data portion of Oracle SQL Fundamentals I certification include:

• Displaying Data from Multiple Tables
  • Write SELECT statements to access data from more than one table using equijoins and nonequijoins
  • Join a table to itself by using a self-join
  • View data that generally does not meet a join condition by using outer joins
  • Generate a Cartesian product of all rows from two or more tables

Readings

1. Read Wikipedia: Join (SQL).
2. Read Oracle: Joins.
3. Read Guide to SQL Joins.

Multimedia

1. YouTube: Oracle SQL Tutorials - Joining Tables Theory - Part 1
2. YouTube: Oracle SQL Tutorial - Joining Tables Theory - Part 2
3. YouTube: Oracle SQL Tutorial - Joining Tables (Hands-on) - Part 1
4. YouTube: Oracle SQL Tutorial - Joining Tables (Hands-on) - Part 2
5. YouTube: SQL Class - Demo - Explaining Outer Joins and Self Join

Activities

Schema Diagrams

1. Review HR and OE schema diagrams.
   1. Review the Oracle: HR and OE schema diagrams.

HR Joins

Natural Joins

1. Test natural joins of two tables using HR data.
   1. Join the regions and countries tables with a WHERE clause using the following query:
      SELECT R.REGION_NAME, C.COUNTRY_NAME
FROM REGIONS R, COUNTRIES C
WHERE R.REGION_ID = C.REGION_ID
ORDER BY R.REGION_NAME, C.COUNTRY_NAME;
   2. Join the regions and countries tables with a NATURAL JOIN clause using the following query:
      SELECT R.REGION_NAME, C.COUNTRY_NAME
FROM REGIONS R NATURAL JOIN COUNTRIES C
ORDER BY R.REGION_NAME, C.COUNTRY_NAME;
   3. Join the regions and countries tables with a JOIN USING clause using the following query:
      SELECT R.REGION_NAME, C.COUNTRY_NAME
FROM REGIONS R JOIN COUNTRIES C USING (REGION_ID)
ORDER BY R.REGION_NAME, C.COUNTRY_NAME;
   4. Join the regions and countries tables with a JOIN ON clause using the following query:
      SELECT R.REGION_NAME, C.COUNTRY_NAME
FROM REGIONS R JOIN COUNTRIES C ON (R.REGION_ID = C.REGION_ID)
ORDER BY R.REGION_NAME, C.COUNTRY_NAME;
2. Test natural joins of three tables using HR data.
   1. Join the regions, countries, and locations tables with a WHERE clause using the following query:
      SELECT R.REGION_NAME, C.COUNTRY_NAME, L.CITY
FROM REGIONS R, COUNTRIES C, LOCATIONS L
WHERE R.REGION_ID = C.REGION_ID
AND C.COUNTRY_ID = L.COUNTRY_ID
ORDER BY R.REGION_NAME, C.COUNTRY_NAME, L.CITY;
   2. Join the regions, countries, and locations tables with a NATURAL JOIN clause using the following query:
      SELECT R.REGION_NAME, C.COUNTRY_NAME, L.CITY
FROM REGIONS R NATURAL JOIN COUNTRIES C NATURAL JOIN LOCATIONS L
ORDER BY R.REGION_NAME, C.COUNTRY_NAME, L.CITY;
   3. Join the regions, countries, and locations tables with a JOIN USING clause using the following query:
      SELECT R.REGION_NAME, C.COUNTRY_NAME, L.CITY
FROM REGIONS R JOIN COUNTRIES C USING (REGION_ID) JOIN LOCATIONS L USING (COUNTRY_ID)
ORDER BY R.REGION_NAME, C.COUNTRY_NAME, L.CITY;
   4. Join the regions, countries, and locations tables with a JOIN ON clause using the following query:
      SELECT R.REGION_NAME, C.COUNTRY_NAME, L.CITY
FROM REGIONS R JOIN COUNTRIES C ON (R.REGION_ID = C.REGION_ID)
JOIN LOCATIONS L ON (C.COUNTRY_ID = L.COUNTRY_ID)
ORDER BY R.REGION_NAME, C.COUNTRY_NAME, L.CITY;

Nonequijoins

1. Test nonequijoins using HR data.
   1. Select employees whose salary is less than the minimum salary of other positions and list the position using the following query:
      SELECT E.LAST_NAME, E.SALARY, J.MIN_SALARY, J.JOB_TITLE
FROM EMPLOYEES E JOIN JOBS J ON (E.SALARY < J.MIN_SALARY)
ORDER BY J.JOB_TITLE, E.SALARY, E.LAST_NAME;

Self Joins

1. Test self joins using HR data.
   1. Select manager names and employee names from the employees table using the following query:
      SELECT M.LAST_NAME AS MANAGER, E.LAST_NAME AS EMPLOYEE
FROM EMPLOYEES E JOIN EMPLOYEES M ON (E.MANAGER_ID = M.EMPLOYEE_ID)
ORDER BY M.LAST_NAME, E.LAST_NAME;

Outer Joins

1. Test outer joins using HR data.
   1. Select all departments and the last names of employees in those departments. Include departments with no employees using ANSI syntax in the following query:
      SELECT D.DEPARTMENT_NAME, E.LAST_NAME
FROM DEPARTMENTS D
LEFT OUTER JOIN EMPLOYEES E ON (D.DEPARTMENT_ID = E.DEPARTMENT_ID)
ORDER BY D.DEPARTMENT_NAME, E.LAST_NAME;
   2. Select all departments and the last names of employees in those departments. Include departments with no employees using Oracle syntax in the following query:
      SELECT D.DEPARTMENT_NAME, E.LAST_NAME
FROM DEPARTMENTS D, EMPLOYEES E
WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID (+)
ORDER BY D.DEPARTMENT_NAME, E.LAST_NAME;
   3. Select the last names of all employees and their departments. Include employees with no department using ANSI syntax in the following query:
      SELECT D.DEPARTMENT_NAME, E.LAST_NAME
FROM DEPARTMENTS D
RIGHT OUTER JOIN EMPLOYEES E ON (D.DEPARTMENT_ID = E.DEPARTMENT_ID)
ORDER BY D.DEPARTMENT_NAME NULLS FIRST, E.LAST_NAME;
   4. Select the last names of all employees and their departments. Include employees with no department using Oracle syntax in the following query:
      SELECT D.DEPARTMENT_NAME, E.LAST_NAME
FROM DEPARTMENTS D, EMPLOYEES E
WHERE D.DEPARTMENT_ID (+) = E.DEPARTMENT_ID
ORDER BY D.DEPARTMENT_NAME NULLS FIRST, E.LAST_NAME;
   5. Select all departments and the last names of all employees. Include departments with no employees and employees with no departments using the following query:
      SELECT D.DEPARTMENT_NAME, E.LAST_NAME
FROM DEPARTMENTS D
FULL OUTER JOIN EMPLOYEES E ON (D.DEPARTMENT_ID = E.DEPARTMENT_ID)
ORDER BY D.DEPARTMENT_NAME NULLS FIRST, E.LAST_NAME;
   6. Select the last name of employees with no department using the following query:
      SELECT E.LAST_NAME
FROM EMPLOYEES E
LEFT OUTER JOIN DEPARTMENTS D ON (E.DEPARTMENT_ID = D.DEPARTMENT_ID)
WHERE D.DEPARTMENT_ID IS NULL;

Cross Joins

1. Test cross joins using HR data.
   1. Select the number of employees using the following query:
      SELECT COUNT(*) AS COUNT FROM EMPLOYEES;
   2. Select the number of employees in departments using the following query:
      SELECT COUNT(*) AS COUNT
FROM EMPLOYEES E
JOIN DEPARTMENTS D ON (E.DEPARTMENT_ID = D.DEPARTMENT_ID);
   3. Select the number of employees times the number of departments using the following query:
      SELECT COUNT(*) AS COUNT
FROM EMPLOYEES CROSS JOIN DEPARTMENTS;
   4. Explain why the following query shows a different number of employees in departments:
      SELECT COUNT(*) AS COUNT
FROM EMPLOYEES NATURAL JOIN DEPARTMENTS;

OE Joins

1. Test natural joins using OE data.
   1. List customers, orders, order items, and product information. Test a variety of join types until you are comfortable with the syntax.
   2. List warehouse, product, and inventory availability. Test a variety of join types until you are comfortable with the syntax.
2. Test nonequijoins using OE data.
3. Test self joins using OE data.
4. Test outer joins using OE data.
   1. List customers, orders, order items, and product information. Test a variety of ANSI and Oracle outer join types until you are comfortable with the syntax.
   2. List warehouse, product, and inventory availability. Test a variety of ANSI and Oracle outer join types until you are comfortable with the syntax.
5. Test cross joins using OE data.

Lesson Summary

• A SQL join clause combines columns from one or more tables in a relational database.
• ANSI-standard SQL specifies five types of JOIN: INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER and CROSS. As a special case, a table (base table, view, or joined table) can JOIN to itself in a self-join.
• An inner join contains each row in the two joined tables that have matching rows.
• A left outer join contains all rows of the "left" table, even if the join-condition does not find any matching row in the "right" table.
• A right outer join contains all rows of the "right" table, even if the join-condition does not find any matching row in the "left" table.
• A full outer join combines the effect of applying both left and right outer joins, containing all rows of both tables, and joined where there are matching rows.
• A cross join combines each row from the first table with every row from the second table.
• A self-join is joining a table to itself.
• An equi-join is a specific type of comparator-based join that uses equality comparisons in the join-predicate.
• A non-equi-join uses non-equality comparison operators (such as < or >) in the join-predicate.
• WHERE syntax: SELECT <field(s)> FROM <table1>, <table2> WHERE <table1.field> = <table2.field>;
• NATURAL JOIN syntax: SELECT <field(s)> FROM <table1> NATURAL JOIN <table2>;
• JOIN USING syntax: SELECT <field(s)> FROM <table1> JOIN <table2> USING (<column>);
• JOIN ON syntax: SELECT <field(s)> FROM <table1> JOIN <table2> ON (<table1.field> = <table2.field>);
• LEFT OUTER JOIN syntax: SELECT <field(s)> FROM <table1> LEFT OUTER JOIN <table2> ON (<table1.field> = <table2.field>);
• RIGHT OUTER JOIN syntax: SELECT <field(s)> FROM <table1> RIGHT OUTER JOIN <table2> ON (<table1.field> = <table2.field>);
• FULL OUTER JOIN syntax: SELECT <field(s)> FROM <table1> FULL OUTER JOIN <table2> ON (<table1.field> = <table2.field>);
• Explicit CROSS JOIN syntax: SELECT <field(s)> FROM <table1> CROSS JOIN <table2>;
• Implicit CROSS JOIN syntax: SELECT <field(s)> FROM <table1>, <table2>;
• Self-join syntax: SELECT <field(s> FROM <table1 AS alias1> INNER JOIN <table1 AS alias2> ON (<alias1.field> = <alias2.field>);
• Oracle deprecated left-outer-join syntax: SELECT <field(s)> FROM <table1>, <table2> WHERE <table1.field> = <table2.field> (+)
• Oracle deprecated right-outer-join syntax: SELECT <field(s)> FROM <table1>, <table2> WHERE <table1.field> (+) = <table2.field>
• Non-equi-join example: SELECT <field(s)> FROM <table1> JOIN <table2> ON (<table1.field> < <table2.field>);

Assessments

• Flashcards: Quizlet: Oracle 1Z0-051 Exam - Joins

See Also

• Oracle: Database 2 Day Developers Guide - Selecting Table Data

References

← Aggregating Data

Oracle SQL Fundamentals

Subqueries →