Equijoins

Contact Us or call 1-877-932-8228
Equijoins

Equijoins

A number of different types of joins use the equals operator. The most common is the inner join. Oracle has an "old" syntax for joins but also supports ANSI standard SQL joins. There are actually some additional distinctions for different types of equijoins. Oracle's "old" syntax for the inner join conforms to earlier ANSI join syntax. Oracle's "old" syntax for the outer join is proprietary. It is based on the earlier ANSI inner join syntax. The earlier ANSI inner join syntax is often referred to as a "Theta Join". The newer ANSI join syntax is referred to as a "SQL99 Join".

Inner Joins

With an inner join, columns with a matching row in each joined table are returned. An inner join is the default type of join, so the INNER keyword may be omitted in the SQL99 version.

For example, list country and region information.

Code Sample:

Joining-Tables/Demos/inner_join.sql
-- Earlier ANSI Join Syntax (Theta Join)

SELECT * 
FROM regions r, countries c 
WHERE r.region_id = c.region_id;

-- New (ANSI SQL99) Syntax - the INNER keyword is optional

SELECT * 
FROM regions r 
INNER JOIN countries c 
ON r.region_id = c.region_id;

An inner join causes columns that have a matching row in each joined table to be returned. Traditionally this type of query was specified in Oracle as part of the WHERE clause using the earlier ANSI standard (Theta Join) syntax. SQL99 ANSI standard join syntax was added in Oracle 9i. An inner join can now be specified in a separate JOIN clause rather than including it among filtering predicates in the WHERE clause.

Outer Joins

Code Sample:

Joining-Tables/Demos/count_employees.sql
--
-- This query returns a count of 107 records in the employees table
--
SELECT count(*) 
FROM employees;
 
--   
--When we make an inner join to departments, we only get 106!
--
SELECT count(*) 
FROM employees e 
INNER JOIN departments d ON e.department_id = d.department_id;

--
-- What happened to the one record?  
-- As it turns out, one record has a null department_id.
--
SELECT * 
FROM employees 
WHERE department_id IS NULL;

Start by checking how many records are in the employees table. Next, create an inner join with the department table and check the count again. There appears to be a record missing. The missing record has a null in the department_id field, and therefore no corresponding row in the departments table. By using a left outer join, this record is returned as well.

In order to restrict the number of rows returned in the following queries, filtering predicate expressions (criteria) are added to each WHERE clause. However, keep in mind that an outer query returns all of the records returned by an inner query PLUS certain additional records from one or both of the tables.

Code Sample:

Joining-Tables/Demos/outer_join.sql
--
-- The employee can be retrieved when the department_id is ignored...
--
SELECT first_name, last_name 
FROM employees e    
WHERE e.employee_id = 178;

--   
-- ...but the employee is not returned when a join is made to the 
-- department table because there is no matching record in the
-- department table
--
SELECT first_name, last_name 
FROM employees e    
INNER JOIN departments d ON d.department_id = e.department_id
WHERE e.employee_id = 178;

--   
-- By using a LEFT OUTER JOIN we include all records that are included
-- in an inner join plus all records in the employees table.
--    
SELECT first_name, last_name 
FROM employees e    
LEFT OUTER JOIN departments d ON d.department_id = e.department_id
WHERE e.employee_id = 178;

--    
-- In order to demonstrate the right and full outer joins, it is necessary
-- to add a record to the departments table that does not have any 
-- employee records associated with it.
--    
INSERT INTO departments (
	department_id, department_name, manager_id, location_id
) 
VALUES 
(
	999,'Vacant Dept', NULL, NULL
);
    
--
--  This query returns no records
--
SELECT first_name, last_name, department_name
FROM employees e 
INNER JOIN departments d ON d.department_id = e.department_id
WHERE e.employee_id = 178
OR d.department_id = 999; 

--
-- Using a LEFT OUTER JOIN records are returned from the employee 
-- table with no associated record in the department table
--
SELECT first_name, last_name, department_name
FROM employees e 
LEFT OUTER JOIN departments d ON d.department_id = e.department_id
WHERE e.employee_id = 178
OR d.department_id = 999; 

--
-- Using a RIGHT OUTER JOIN records are returned from the deparment 
-- table with no associated record in the employees table
--
SELECT first_name, last_name, department_name
FROM employees e 
RIGHT OUTER JOIN departments d ON d.department_id = e.department_id
WHERE e.employee_id = 178
OR d.department_id = 999;    
    
--
-- This query returns records in the deparment table with no associated
-- record in the employees table as well as records in the employees
-- table with no record in the departments table
--
SELECT first_name, last_name, department_name
FROM employees e 
FULL OUTER JOIN departments d ON d.department_id = e.department_id
WHERE e.employee_id = 178
OR d.department_id = 999;
	
--
-- Using Oracle proprietary syntax, a left outer join can also be specified using (+) to indicate
-- the table column where a null is acceptable
--

SELECT first_name, last_name, department_name
FROM employees e , departments d
WHERE (e.employee_id = 178
OR d.department_id = 999)
AND d.department_id = e.department_id(+)

A generally accepted "Best Practice" is to avoid nulls in the database. However, real world situations result in occasions where parent records have no associated child records. An employee may not have an assigned department (if they have retired), and a department may not have an associated employee (if the department is no longer in use but still has relevant data associated with it).

These situations require the use of outer joins as illustrated in these examples.

Next