Oracle SQL Joins

There are seven different types of Oracle sql joins: inner joins, outer joins, equijoins, self joins,  cross joins, anti joins and semi joins. Each of these join types


combines the data in a different way. In relational theory (on which SQL and therefore the Oracle database are based) a join refers to the combination of two sets of data (which may be tables, views, sub queries or materialized views) in various ways.

This tutorial will look at each of these Oracle sql joins in detail.

Oracle Inner Join

This is the most common

type of SQL join. It is also known as the simple join and with this type of join the 2 sets of data are combined and only those members that have a matching value for a specified attribute are kept.

Let's have a look at an example to make this more clear. Let's assume our Oracle database has two tables emp which contains employee details and dept which contains details of the different departments in our organisation. The structure of these tables is as follows:

CREATE TABLE DEPT 
     (dept_id   NUMBER
 PRIMARY KEY
     ,dept_name VARCHAR2(30))

CREATE TABLE EMP
    (emp_id     NUMBER
 PRIMARY KEY
    ,first_name VARCHAR2(30)
    ,last_name  VARCHAR2(30)
    ,dept_id    NUMBER) 

And with the following data:

DEPT
Dept_id Dept_name
1 HR
2 Marketing
3 Sales
4 Finance
5 IT
EMP
emp_id first_name last_name mgr_id dept_id
1 Michelle Jones 9 1
2 Bill Henderson 3 3
3 Kevin Radley 9 3
4 Francis Potter 6 5
5 Linda Jackson 9 5
6 Jason Jennings 6 5
7 Tracey Lee 9 4
8 Thomas Atkins 9
9 Raj Patel 0 5

An inner join of these 2 tables like so:

SELECT first_name||' '||last_name emp_name
      ,dept_name 

FROM   emp JOIN dept USING (dept_id)

would produce the following results:

EMP_NAME DEPT_NAME
Michelle Jones HR
Bill Henderson Sales
Kevin Radley Sales
Francis Potter IT
Linda Jackson IT
Jason Jennings IT
Tracey Lee Finance
Raj Patel IT

In other words the result set (remember SQL is based on sets - or tuples in relational terminology) includes only those records that have a match on the dept_id. As nobody has been assigned to the marketing department in our (mythical) organisation, that row doesn't appear in the result set and neither does Thomas Atkins as he has not been assigned to a department.

Equi join

The example above is also an equi join - because we're only interested in those rows that have the same value for dept_id.

This is better demonstrated by using the Oracle sql joins syntax

SELECT first_name||' '||last_name emp_name
      ,dept_name 

FROM   emp
      ,dept
WHERE emp.dept_id = dept.dept_id

In this case we can see the explicit equals condition that specifies the department id must match.


Oracle Tips & Tricks to SKYROCKET Your Career!

If you're not already a subscriber to our ezine you're missing out on a myriad of tips and tricks in SQL and PL/SQL to help you become a better, faster, smarter Oracle developer. Subscribe now and ignite your career.


Non-equijoins

Non-equijoins are one of the Oracle sql joins not used very often because few business questions require them and the results are not easy to interpret even if they make sense.

For example we could replace the equals sign in our previous query with not equals (or with the less than or greater than operator) as follows

SELECT first_name||' '||last_name emp_name, dept_name 
FROM   emp, dept WHERE emp.dept_id <> dept.dept_id

You might think this would just return those employees not assigned to a department but that is not the case. Instead it will return a record for each employee for each department other than the one he or she is assigned to. As this is not very useful, non-equi joins are seldom used. 

Cross Joins

Another one of the Oracle sql joins not so useful is the cross join, also known as the Cartesian product (named after the 17th century French philosopher/mathematician Rene Descartes) or just the product of two data sets.

Why is this one not so useful? Well, the result set contains one row for each possible combination of the data sets i.e. the product in mathematical terms. Therefore if one table with 10 rows is cross joined table with 10 rows, the result set will contain 100 rows.

A cross join is achieved by not specifying any conditions for the join. 

SELECT first_name||' '||last_name emp_name
      ,dept_name 

FROM   emp
      ,dept


Oracle has to do a large amount of work to calculate the product even for small data sets and this could affect database performance significantly.

Outer Joins

Outer joins are a very useful type of the Oracle sql joins. These are used to  return all rows from one or both sides of the join whether or not a match is found.

There are 3 subtypes depending on whether we want a full outer join or a partial outer join: left outer join, right outer join and full outer join.

Let's look at an example reusing the tables and data from our first example. In that example we wanted to know who worked for which department, but we didn't get a complete list of the departments because nobody had been assigned to the marketing department. Let's assume then that we want a list of all the departments including those to which no one has been assigned. We can achieve this with an outer join type of Oracle sql joins as follows:

SELECT first_name||' '||last_name emp_name
      ,dept_name 

FROM   emp RIGHT OUTER JOIN dept USING (dept_id)

which would produce the following results:

EMP_NAME DEPT_NAME
Michelle Jones HR
Bill Henderson Sales
Kevin Radley Sales
Francis Potter IT
Linda Jackson IT
Jason Jennings IT
Tracey Lee Finance
Raj Patel IT

Marketing

As you can see this time "Marketing" appears with no corresponding employee.

That was a right outer sql join as dept was on the right hand side of the query.

A left outer join in this case would return all employees including those who haven't been assigned to a department (or have been assigned to a department that isn't in the department table).

SELECT first_name||' '||last_name emp_name
      ,dept_name 

FROM   emp LEFT OUTER JOIN dept USING (dept_id)

which would produce the following results:

EMP_NAME DEPT_NAME
Michelle Jones HR
Bill Henderson Sales
Kevin Radley Sales
Francis Potter IT
Linda Jackson IT
Jason Jennings IT
Tracey Lee Finance
Raj Patel IT
Thomas Atkins

If we wanted the full set (all the missing records on both sides of the join) then we would use a full outer join

SELECT first_name||' '||last_name emp_name
      ,dept_name 

FROM   emp OUTER JOIN dept USING (dept_id)

This would produce the same data as before plus a record for the marketing department.

Self Joins

This is another of the types of Oracle SQL joins that is reasonably common. In this case, the table is joined to itself. This is usually done for hierarchical (parent/child) queries where the relationship is stored in the same table.
  
For example, in our emp table the mgr_id column refers to the emp_id column of that employees manager. Therefore Tracey Lee's manager is Raj Patel. To retrieve both the employee name and the manager's name therefore requires a self join as follows.

SELECT emp.first_name||' '||emp.last_name emp_name
      ,mgr.first_name
||' '||mgr.last_name mgr_name
FROM   emp JOIN emp mgr ON (emp_id=mgr.emp_id)


which would produce the following results:

EMP_NAME MGR_NAME
Michelle Jones Raj Patel
Bill Henderson Kevin Radley
Kevin Radley Raj Patel
Francis Potter Jason Jennings
Linda Jackson Jason Jennings
Jason Jennings Raj Patel
Tracey Lee Raj Patel
Thomas Atkins Raj Patel

Anti joins

An sql anti join results from a sub query used with the NOT IN clause. In this case, only rows from the left side of the Oracle sql joins are returned.

For example:

SELECT first_name||' '||last_name emp_name
FROM   emp
WHERE dept_id NOT IN
 (SELECT dept_id FROM dept WHERE dept_name = 'HR')


would return the names of all employees who are not in the HR department.

EMP_NAME
Bill Henderson
Kevin Radley
Francis Potter
Linda Jackson
Jason Jennings
Tracey Lee
Raj Patel
Thomas Atkins

Semi join

A semi join in Oracle sql is another query optimization technique. In this case it is used for EXISTS queries. The results set contains rows from the left side of the query which match the results of an EXISTS sub query without duplicating rows even when multiple rows from the sub query are returned.

For example:

SELECT first_name||' '||last_name emp_name

FROM  emp WHERE EXISTS 
 (SELECT dept_id FROM dept 
  WHERE dept_name = 'HR' AND emp.dept_id = dept.dept_id)

EMP_NAME
Michelle Jones

As well as reading our other Oracle tutorials, one of the best ways to learn more about Oracle sql joins is to attend a formal SQL training course. Smartsoft Computing offer Oracle SQL training in the UK both on and off-site.


Looking for Oracle training in the UK? Learn Oracle with Smartsoft. Advance your skills and sky rocket your career.


Return from Oracle SQL joins to advanced Oracle tutorials #1
Return to home page