An Introduction to Oracle Joins

Last update on August 02 2024 13:00:27 (UTC/GMT +8 hours)

Displaying data from multiple tables using Joins

The purpose of a join is to combine the data from two or more tables, views, or materialized views. A join is actually performed whenever multiple tables appear in the FROM clause of the query and by the where clause which combines the specified rows of tables. If a join involves in more than two tables then Oracle joins first two tables based on the joins condition and then compares the result with the next table and so on. If any two of the table participating in joining with a column name in common, then it is necessary to maintain all references to these columns throughout the query with table names to avoid ambiguity.

In this lesson, we have discussed how to obtain data from more than one table using JOIN and a brief description with the example of every join. In the next articles we have explained all JOINs and their uses thoroughly.

Note: Before the Oracle9i release, the join syntax was different from the American National Standards Institute (ANSI) standards. The SQL:1999 compliant join syntax does not offer any performance benefits over the Oracle-proprietary join syntax that existed in the prior releases. Check the individual join page for detailed information about the proprietary join syntax.

Join Conditions

A join queries must have contained at least one join condition, either in the FROM clause or in the WHERE clause. The join condition compares two columns from two different tables. The Oracle Database combines pairs of rows, from each table, participating in joining, which are satisfying the join condition evaluates to TRUE.

A WHERE clause that contains a join condition can also contain other conditions that refer to columns of only one table. These conditions can further restrict the rows returned by the join query.

Types of JOIN

Equijoins
Non-Equi Joins
Self Joins
Cross Join / Cartesian Products
Inner Joins
Outer Joins
- Left Outer Join
- Right Outer Join
- Full Outer Join
Natural Join
Antijoins
Semijoins

Equijoins

An equijoin is a join with a join condition containing an equality operator. This is represented by (=) sign. This join retrieves information by using equality condition.

Sample table: emp_mast

Sample table: dep_mast

Example:

The following command shows that the two tables emp_mast and dep_mast are being joined based on an equality matching criteria i.e., "WHERE e.dept_no=d.dept_no".


-- Select employee number, employee name, job name, and department name
SELECT emp_no, emp_name, job_name, dep_name 
-- From the employee master table with alias 'e' and department master table with alias 'd'
FROM emp_mast e, dep_mast d 
-- Where the department number in the employee master table matches the department number in the department master table
WHERE e.dept_no = d.dept_no;

Explanation:

This SQL query retrieves a list of employees along with their job titles and corresponding department names.

The query joins two tables, emp_mast (employee master) and dep_mast (department master), based on the common column dept_no.

The WHERE clause ensures that only the rows with matching dept_no values from both tables are included in the result set.

The selected columns emp_no, emp_name, job_name, and dep_name provide the desired employee and department details.

Sample Output:

    EMP_NO EMP_NAME             JOB_NAME   DEP_NAME
---------- -------------------- ---------- ----------
      1234 Alex                 Clerk      FINANCE
      2345 Jack                 Consultant MARKETING
      3456 Paul                 Manager    FINANCE

Non-Equi Join

An nonequi join is an inner join statement that uses an unequal operation (i.e.: <>, >, <, !=, BETWEEN, etc.) to match rows from different tables.

Example:


-- Select employee number, employee name, job name, and department name
SELECT emp_no, emp_name, job_name, dep_name 
-- From the employee master table with alias 'e' and department master table with alias 'd'
FROM emp_mast e, dep_mast d 
-- Where the department number in the employee master table is greater than the department number in the department master table
WHERE e.dept_no > d.dept_no;

Explanation:

This SQL query retrieves a list of employees along with their job titles and corresponding department names.

It joins two tables, emp_mast (employee master) and dep_mast (department master), but only includes rows where the dept_no in the emp_mast table is greater than the dept_no in the dep_mast table.

This non-equi join condition (e.dept_no > d.dept_no) results in a comparison that doesn't require equality, thus potentially producing a Cartesian product filtered by this condition.

The selected columns (emp_no, emp_name, job_name, and dep_name) provide the relevant employee and department information.

Sample Output:

    EMP_NO EMP_NAME             JOB_NAME   DEP_NAME
---------- -------------------- ---------- ----------
      2345 Jack                 Consultant FINANCE
      4567 Jenefer              Engineer   FINANCE
      4567 Jenefer              Engineer   MARKETING
      4567 Jenefer              Engineer   HR

Self Joins

A self join is such a join in which a table is joined with itself. For example, when you require details about an employee and his manager (also an employee).

Example:


-- Select employee number from the first instance of the employee master table with alias 'a1'
SELECT a1.emp_no, 
-- Select employee name from the second instance of the employee master table with alias 'a2'
a2.emp_name, 
-- Select job name from the first instance of the employee master table with alias 'a1'
a1.job_name, 
-- Select department number from the second instance of the employee master table with alias 'a2'
a2.dept_no 
-- From the employee master table with two instances: alias 'a1' and alias 'a2'
FROM emp_mast a1, emp_mast a2 
-- Where the employee number in the first instance is equal to the manager ID in the second instance
WHERE a1.emp_no = a2.mgr_id;

Explanation:

This SQL query performs a self join on the emp_mast (employee master) table.

It retrieves the employee number, employee name, job name, and department number for pairs of employees where one employee (from the second instance of the table a2) is the manager of the other employee (from the first instance of the table a1).

The condition a1.emp_no = a2.mgr_id ensures that only the rows where emp_no matches the mgr_id are selected, effectively linking employees to their managers.

Sample Output:

    EMP_NO EMP_NAME             JOB_NAME      DEPT_NO
---------- -------------------- ---------- ----------
      4567 Alex                 Engineer           15
      3456 Jack                 Manager            25
      1234 Paul                 Clerk              15
      2345 Jenefer              Consultant         45

Cross Joins

A Cross Join or Cartesian join or Cartesian product is a join of every row of one table to every row of another table.

Example:


-- Select employee number from the employee master table
SELECT emp_no, 
-- Select employee name from the employee master table
emp_name, 
-- Select job name from the employee master table
job_name, 
-- Select department name from the department master table
dep_name, 
-- Select location from the department master table
location 
-- From the employee master table
FROM emp_mast 
-- Perform a cross join with the department master table
CROSS JOIN dep_mast;

Explanation:

This SQL query performs a cross join between the emp_mast (employee master) table and the dep_mast (department master) table.

A cross join generates the Cartesian product of the two tables, meaning it combines each row from emp_mast with every row from dep_mast.

The result includes columns for the employee number, employee name, job name, department name, and location for every possible combination of employees and departments.

Sample Output:

    EMP_NO EMP_NAME             JOB_NAME   DEP_NAME   LOCATION
---------- -------------------- ---------- ---------- ---------
      1234 Alex                 Clerk      FINANCE    PARIS
      2345 Jack                 Consultant FINANCE    PARIS
      3456 Paul                 Manager    FINANCE    PARIS
      4567 Jenefer              Engineer   FINANCE    PARIS
      1234 Alex                 Clerk      MARKETING  LONDON
      2345 Jack                 Consultant MARKETING  LONDON
      3456 Paul                 Manager    MARKETING  LONDON
      4567 Jenefer              Engineer   MARKETING  LONDON
      1234 Alex                 Clerk      HR         DELHI
      2345 Jack                 Consultant HR         DELHI
      3456 Paul                 Manager    HR         DELHI
      4567 Jenefer              Engineer   HR         DELHI

12 rows selected.

Inner Joins

An inner join is a join that returns rows of the tables that satisfy the join condition.

Example:


-- Select employee number from the employee master table
SELECT emp_no, 
-- Select employee name from the employee master table
emp_name, 
-- Select job name from the employee master table
job_name, 
-- Select department name from the department master table
dep_name, 
-- Select location from the department master table
location 
-- From the employee master table
FROM emp_mast 
-- Perform an inner join with the department master table using the common dept_no column
INNER JOIN dep_mast USING(dept_no);

Explanation:

This SQL query performs an inner join between the emp_mast (employee master) table and the dep_mast (department master) table based on the dept_no column, which is common to both tables.

The query retrieves the employee number, employee name, job name, department name, and location for employees whose dept_no matches the dept_no in the department master table.

Only the rows where the dept_no is present in both tables will be included in the result set.

Sample Output:

    EMP_NO EMP_NAME             JOB_NAME   DEP_NAME   LOCATION
---------- -------------------- ---------- ---------- ----------
      1234 Alex                 Clerk      FINANCE    PARIS
      2345 Jack                 Consultant MARKETING  LONDON
      3456 Paul                 Manager    FINANCE    PARIS

Outer Joins

An outer join is such a join which is similar to the equi join, but Oracle will also return non matched rows from the table.

Left Outer Join

This left outer join displays all matching records of both table along with the records in left hand side table of join clause which are not in right hand side table of join clause.

Example:


-- Select employee number from the employee master table
SELECT emp_no, 
-- Select employee name from the employee master table
emp_name, 
-- Select job name from the employee master table
job_name, 
-- Select department name from the department master table
dep_name, 
-- Select location from the department master table
location 
-- From the employee master table with alias 'e'
FROM emp_mast e 
-- Perform a left outer join with the department master table with alias 'd' on the condition that dept_no matches in both tables
LEFT OUTER JOIN dep_mast d 
ON (e.dept_no = d.dept_no);

Explanation:

This SQL query performs a left outer join between the emp_mast (employee master) table and the dep_mast (department master) table based on the dept_no column.

The query selects the employee number, employee name, job name, department name, and location.

It retrieves all rows from the emp_mast table, and the matching rows from the dep_mast table based on the dept_no column.

If there is no match, the result will still include the employee information from emp_mast, but the dep_mast columns (dep_name and location) will contain NULL values.


-- Select employee number from the employee master table
SELECT emp_no, 
-- Select employee name from the employee master table
emp_name, 
-- Select job name from the employee master table
job_name, 
-- Select department name from the department master table
dep_name, 
-- Select location from the department master table
location 
-- From the employee master table with alias 'e'
FROM emp_mast e, 
-- From the department master table with alias 'd'
dep_mast d 
-- Perform a left outer join where the department number from employee master table matches with the department number from department master table
WHERE e.dept_no = d.dept_no(+);

Explanation:

This SQL query performs a left outer join between the emp_mast (employee master) table and the dep_mast (department master) table using the old Oracle syntax for outer joins.

The query selects the employee number, employee name, job name, department name, and location.

It retrieves all rows from the emp_mast table and the matching rows from the dep_mast table based on the dept_no column.

The (+) symbol indicates an outer join, meaning that if there is no match for a row in emp_mast, the result will still include that row, but the dep_mast columns (dep_name and location) will contain NULL values.

Sample Output:

    EMP_NO EMP_NAME             JOB_NAME   DEP_NAME   LOCATION
---------- -------------------- ---------- ---------- ----------
      3456 Paul                 Manager    FINANCE    PARIS
      1234 Alex                 Clerk      FINANCE    PARIS
      2345 Jack                 Consultant MARKETING  LONDON
      4567 Jenefer              Engineer

Right Outer Join

This right outer join displays all matching records of both tables along with the records in left hand side table of join clause which are not in right hand side table of join clause.

Example:


-- Select employee number from the employee master table
SELECT emp_no, 
-- Select employee name from the employee master table
emp_name, 
-- Select job name from the employee master table
job_name, 
-- Select department name from the department master table
dep_name, 
-- Select location from the department master table
location 
-- From the employee master table with alias 'e'
FROM emp_mast e 
-- Perform a right outer join with the department master table with alias 'd'
RIGHT OUTER JOIN dep_mast d 
-- On the condition where the department number in the employee master table matches the department number in the department master table
ON(e.dept_no = d.dept_no);

Explanation:

This SQL query performs a right outer join between the emp_mast (employee master) table and the dep_mast (department master) table.

It selects the employee number, employee name, job name, department name, and location.

The query retrieves all rows from the dep_mast table and the matching rows from the emp_mast table based on the dept_no column.

If there is no match for a row in dep_mast, the result will still include that row, but the emp_mast columns (emp_no, emp_name, and job_name) will contain NULL values.


-- Select employee number from the employee master table
SELECT emp_no, 
-- Select employee name from the employee master table
emp_name, 
-- Select job name from the employee master table
job_name, 
-- Select department name from the department master table
dep_name, 
-- Select location from the department master table
location 
-- From the employee master table with alias 'e' and department master table with alias 'd'
FROM emp_mast e, dep_mast d 
-- Perform a right outer join where the department number in the employee master table matches the department number in the department master table
WHERE e.dept_no(+) = d.dept_no;

Explanation:

This SQL query performs a right outer join between the emp_mast (employee master) table and the dep_mast (department master) table using the Oracle proprietary syntax for outer joins.

It selects the employee number, employee name, job name, department name, and location.

The (+) operator is used to indicate that the dept_no column in the emp_mast table should include NULL values when there is no corresponding dept_no in the dep_mast table.

This means the query will return all rows from the dep_mast table and the matching rows from the emp_mast table.

If there is no match, the result will still include that row from dep_mast with NULL values for the columns from emp_mast.

Sample Output:

    EMP_NO EMP_NAME             JOB_NAME   DEP_NAME   LOCATION
---------- -------------------- ---------- ---------- ---------
      1234 Alex                 Clerk      FINANCE    PARIS
      2345 Jack                 Consultant MARKETING  LONDON
      3456 Paul                 Manager    FINANCE    PARIS
                                           HR         DELHI

Full Outer Join

A full outer join returns all rows from both the tables left and right side of the join clause, extended with nulls if they do not satisfy the join condition.

Example:


-- Select employee number from the employee master table
SELECT emp_no, 
-- Select employee name from the employee master table
emp_name, 
-- Select job name from the employee master table
job_name, 
-- Select department name from the department master table
dep_name, 
-- Select location from the department master table
location 
-- Perform a full outer join between the employee master table and the department master table
FROM emp_mast e 
FULL OUTER JOIN dep_mast d 
-- Join condition on department number from both tables
ON(e.dept_no = d.dept_no);

Explanation:

This SQL query performs a full outer join between the emp_mast (employee master) table and the dep_mast (department master) table.

It selects the employee number, employee name, job name, department name, and location.

The full outer join ensures that all rows from both tables are included in the result.

If there is no matching department number in one of the tables, the result will contain NULL values for columns from the table where no match was found.

Sample Output:


    EMP_NO EMP_NAME             JOB_NAME   DEP_NAME   LOCATION
---------- -------------------- ---------- ---------- ----------
      1234 Alex                 Clerk      FINANCE    PARIS
      2345 Jack                 Consultant MARKETING  LONDON
      3456 Paul                 Manager    FINANCE    PARIS
      4567 Jenefer              Engineer
                                           HR         DELHI

Natural Join

A natural join is such a join that compares the common columns of both tables with each other.

Example:


-- Select employee number from the employee master table
SELECT emp_no, 
-- Select employee name from the employee master table
emp_name, 
-- Select job name from the employee master table
job_name, 
-- Select department name from the department master table
dep_name, 
-- Select location from the department master table
location 
-- Perform a natural join between the employee master table and the department master table
FROM emp_mast 
NATURAL JOIN dep_mast;

Explanation:

This SQL query performs a natural join between the emp_mast (employee master) table and the dep_mast (department master) table.

It automatically joins the two tables based on all columns with the same name in both tables.

The result includes the employee number, employee name, job name, department name, and location for each matching row in both tables.

If there are columns with the same name in both tables, they are used to match rows.

Sample Output:

    EMP_NO EMP_NAME             JOB_NAME   DEP_NAME   LOCATION
---------- -------------------- ---------- ---------- ----------
      1234 Alex                 Clerk      FINANCE    PARIS
      2345 Jack                 Consultant MARKETING  LONDON
      3456 Paul                 Manager    FINANCE    PARIS

Antijoins

An antijoin between two tables returns rows from the first table where no matches are found in the second table. Anti-Joins are only available when performing a NOT IN sub-query

Example:


-- Select all columns from the employee master table
SELECT * 
FROM emp_mast 
-- Filter rows where the department number is not present in the department master table
WHERE dept_no NOT IN (
    -- Subquery to select all department numbers from the department master table
    SELECT dept_no 
    FROM dep_mast
);

Explanation:

This query retrieves all columns from the emp_mast (employee master) table where the department number (dept_no) does not exist in the dep_mast (department master) table.

It uses a subquery to get the department numbers from dep_mast, and the NOT IN condition filters out rows in emp_mast that have matching department numbers in dep_mast.

Sample Output:

    EMP_NO EMP_NAME             JOB_NAME       MGR_ID    DEPT_NO
---------- -------------------- ---------- ---------- ----------
      4567 Jenefer              Engineer         2345         45

Semijoins

A semi-join is such a join where the EXISTS clause is used with a subquery. It can be called a semi-join because even if duplicate rows are returned in the subquery, only one set of matching values in the outer query is returned.

Example:


-- Select all columns from the department master table
SELECT * 
FROM dep_mast a 
-- Filter rows where there exists at least one row in the employee master table
WHERE EXISTS 
    -- Subquery to check for the existence of matching department numbers in the employee master table
    (SELECT * 
    FROM emp_mast b 
    WHERE a.dept_no = b.dept_no);

Explanation:

This query selects all columns from the dep_mast (department master) table for which there is at least one corresponding row in the emp_mast (employee master) table with a matching department number (dept_no).

The EXISTS clause is used to check for the presence of matching department numbers between the two tables.

Sample Output:

   DEPT_NO DEP_NAME   LOCATION
---------- ---------- ----------
        15 FINANCE    PARIS
        25 MARKETING  LONDON

JOINS: SQL and Other Relational Databases

Previous: NLS_CHARSET_NAME
Next: EQUIJOINS