This Oracle SQL training video tutorial demonstrates how to perform inner joins, outer joins, and self joins to access data from more that one Oracle table where the join conditions contain NULL values.
A relational database consists of SQL tables, and oftentimes SQL tables’ columns have relationships with each other. For example, if you have an EMPLOYEES table that contains a column for a DEPARTMENT_ID, you could have a DEPARTMENTS table that contains either a primary or unique key that is references by the EMPLOYEES’ table. What that means is that IF an employee record has the DEPARTMENT_ID populated, then that value MUST exist in the DEPARTMENTS table.
The syntax for defining an INNER JOIN on these two tables looks like the following:
SELECT e.*, d.* –you could also choose specific columns to display if you like
FROM employees e JOIN departments d –the keyword JOIN is identical to the keywords INNER JOIN
ON e.department_id = d.department_id
In the JOIN condition, if the column names are identical, you could alternately use the following syntax:
USING(department_id)
Another way of thinking about an INNER JOIN is imagine taking the two tables and combining them, giving a result of every combination of records. In other words, if the first table has 30 records and the second table has 20 records, your result is 600 records. This is what we call a CARTESIAN PRODUCT. Usually a cartesian product is NOT what we want! Instead, you want to filter out the unnecessary records by using a WHERE or USING clause. You simply eliminate the records where the join condition is not true.
When using an INNER JOIN, suppose the join condition eliminates records where records contain a NULL value on the join condition. For example, if an employee record’s department_id is NULL, that employee won’t be included in the result set. To ensure that the employee is included, regardless of whether the department_id is null, you perform either a LEFT OUTER JOIN or a RIGHT OUTER JOIN. A LEFT OUTER JOIN includes all records of the table in the left side of the join, whereas a RIGHT OUTER JOIN includes all records of the table in the right side of the join.
Here is the syntax:
SELECT e.*, d.*
FROM employees e LEFT OUTER JOIN departments
USING(department_id)
The above SQL statement ensures that every employee with be displayed, regardless of whether the department_id is populated.
Taking it a step further, suppose you want to perform a join on two columns in the same table. For example, if you employees table contains a column called managed_id, you could display all employees and their manager_id, regardless of whether the employee has a manager:
SELECT e.*, s.*
FROM employees e LEFT OUTER JOIN employees s
ON e.manager_id = s.employee_id
The easiest way to think about SQL SELF JOINS is to imagine you have two copies of a table – one that represents employees, and the other that represents supervisors. You then decide how to join the two tables together. When performing a SELF JOIN, you must give your tables aliases to distinguish them from each other.