# 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

* Read Wikipedia: Join (SQL).
* Read Oracle: Joins.
* Read Guide to SQL Joins.

## Multimedia

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

## Activities
### Schema Diagrams

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

### HR Joins
#### Natural Joins

* Test natural joins of two tables using HR data.
** 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;
** 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;
** 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;
** 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;
* Test natural joins of three tables using HR data.
** 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;
** 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;
** 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;
** 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

* Test nonequijoins using HR data.
** 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

* Test self joins using HR data.
** 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

* Test outer joins using HR data.
** 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;
** 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;
** 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;
** 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;
** 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;
** 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

* Test cross joins using HR data.
** Select the number of employees using the following query: SELECT COUNT(*) AS COUNT FROM EMPLOYEES;
** 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);
** Select the number of employees times the number of departments using the following query: SELECT COUNT(*) AS COUNT FROM EMPLOYEES CROSS JOIN DEPARTMENTS;
** Explain why the following query shows a different number of employees in departments: SELECT COUNT(*) AS COUNT FROM EMPLOYEES NATURAL JOIN DEPARTMENTS;

### OE Joins

* Test natural joins using OE data.
** List customers, orders, order items, and product information. Test a variety of join types until you are comfortable with the syntax.
** List warehouse, product, and inventory availability. Test a variety of join types until you are comfortable with the syntax.
* Test nonequijoins using OE data.
* Test self joins using OE data.
* Test outer joins using OE data.
** 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.
** List warehouse, product, and inventory availability. Test a variety of ANSI and Oracle outer join types until you are comfortable with the syntax.
* 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