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  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

Schema Diagrams

 * 1) Review HR and OE schema diagrams.
 * 2) Review the Oracle: HR and OE schema diagrams.

Natural Joins

 * 1) Test natural joins of two tables using HR data.
 * 2) Join the regions and countries tables with a WHERE clause using the following query:
 * 3) Join the regions and countries tables with a NATURAL JOIN clause using the following query:
 * 4) Join the regions and countries tables with a JOIN USING clause using the following query:
 * 5) Join the regions and countries tables with a JOIN ON clause using the following query:
 * 6) Test natural joins of three tables using HR data.
 * 7) Join the regions, countries, and locations tables with a WHERE clause using the following query:
 * 8) Join the regions, countries, and locations tables with a NATURAL JOIN clause using the following query:
 * 9) Join the regions, countries, and locations tables with a JOIN USING clause using the following query:
 * 10) Join the regions, countries, and locations tables with a JOIN ON clause using the following query:

Nonequijoins

 * 1) Test nonequijoins using HR data.
 * 2) Select employees whose salary is less than the minimum salary of other positions and list the position using the following query:

Self Joins

 * 1) Test self joins using HR data.
 * 2) Select manager names and employee names from the employees table using the following query:

Outer Joins

 * 1) Test outer joins using HR data.
 * 2) Select all departments and the last names of employees in those departments. Include departments with no employees using ANSI syntax in the following query:
 * 3) Select all departments and the last names of employees in those departments. Include departments with no employees using Oracle syntax in the following query:
 * 4) Select the last names of all employees and their departments. Include employees with no department using ANSI syntax in the following query:
 * 5) Select the last names of all employees and their departments. Include employees with no department using Oracle syntax in the following query:
 * 6) Select all departments and the last names of all employees. Include departments with no employees and employees with no departments using the following query:
 * 7) Select the last name of employees with no department using the following query:

Cross Joins

 * 1) Test cross joins using HR data.
 * 2) Select the number of employees using the following query:
 * 3) Select the number of employees in departments using the following query:
 * 4) Select the number of employees times the number of departments using the following query:
 * 5) Explain why the following query shows a different number of employees in departments:

OE Joins

 * 1) Test natural joins using OE data.
 * 2) List customers, orders, order items, and product information. Test a variety of join types until you are comfortable with the syntax.
 * 3) List warehouse, product, and inventory availability. Test a variety of join types until you are comfortable with the syntax.
 * 4) Test nonequijoins using OE data.
 * 5) Test self joins using OE data.
 * 6) Test outer joins using OE data.
 * 7) 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.
 * 8) List warehouse, product, and inventory availability. Test a variety of ANSI and Oracle outer join types until you are comfortable with the syntax.
 * 9) 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 ) in the join-predicate.
 * WHERE syntax: SELECT  FROM, WHERE  = ;
 * NATURAL JOIN syntax: SELECT  FROM NATURAL JOIN ;
 * JOIN USING syntax: SELECT  FROM JOIN USING ;
 * JOIN ON syntax: SELECT  FROM JOIN ON ( = );
 * LEFT OUTER JOIN syntax: SELECT  FROM LEFT OUTER JOIN ON ( = );
 * RIGHT OUTER JOIN syntax: SELECT  FROM RIGHT OUTER JOIN ON ( = );
 * FULL OUTER JOIN syntax: SELECT  FROM FULL OUTER JOIN ON ( = <table2.field>);
 * Explicit CROSS JOIN syntax: SELECT <field(s)> FROM CROSS JOIN ;
 * Implicit CROSS JOIN syntax: SELECT <field(s)> FROM, ;
 * 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, WHERE <table1.field> = <table2.field> (+)
 * Oracle deprecated right-outer-join syntax: SELECT <field(s)> FROM, WHERE <table1.field> (+) = <table2.field>
 * Non-equi-join example: SELECT <field(s)> FROM JOIN ON (<table1.field> < <table2.field>);

Assessments

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