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 SQL 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 syntax for SQL joins.

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

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.

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

Non-equijoins

Non-equijoins are not used very often because the sort of queries/business questions do not often 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 not so useful type of sql join is the cross join, also known as the Cartesian (named after the 17th century French philosopher/mathematician) product 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 joined via a cross join to another 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

This is not generally a good idea especially with large data sets as Oracle has to do a large amount of work to calculate the product.

Outer Joins

Outer joins are a very useful form of sql join. We use them to fill in the blanks and thereby return all rows whether 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.

As an example let's reuse 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 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 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
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 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 sql join 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 is to attend a formal training course. Smartsoft Computing offer Oracle training in the UK both on and off-site as well as expert Oracle training and consultancy in New Zealand .

Oracle SQL Joins

Oracle Inner Join