Join Operations in SQL

Join operations in SQL integrates two relations from the database to form a new relation. The relations are integrated or merged based on the common columns present in the two relations. In the section ahead, we will be discussing all forms of join in SQL.

Types of Join Operations in SQL

1. Natural Join
2. Equi Join
3. Theta Join
4. Semi Join
5. Outer Join

Natural Join

The natural join operation integrates two relations on the basis of common columns present in the relation. Though the relations have common attributes the result of natural join has only one occurrence of the common attribute as the duplicate attribute is eliminated at the time of computation.

Most of the systems require that the common attributes present in the relations on which natural join has to be performed must have the same name as it becomes easy to identify the attributes that are used to perform join.

In this case, if the common attributes have different names you have to use rename clause. Though in some systems natural join allows the names of common attributes to be used for joining to have different names they must have the same domain.

Now let us understand the natural join with the help of an example. We have two relations professor(prof_id, first_name, last_name, section) and student(stud_id, prof_id, first_name, last_name). Now, here the common attributes are a professor.prof_id and student.prof_id.

The query applying natural join on the relations professor and student is as follow:

SELECT *
FROM professor
JOIN student
ON professor.prof_id= student.prof_id;

This join query would result in the following relation.

Now you can see that the table above has so many columns so we can modify the query to display some specific attributes only as:

SELECT p.first_name, p.last_name,
s.first_name, s.last_name
FROM professor AS p
JOIN student AS s
ON p.prof_id= s.prof_id;

The result of the modified query is given below.

But there is again a problem we have two first_names and two last_names which is creating confusion. So, let us again modify this query.

SELECT
p.first_name AS prof_fn, p.last_name AS prof_ln,
s.first_name AS stud_fn, s.last_name AS stud_ln
FROM professor AS p
JOIN student AS s
ON p.prof_id= s.prof_id;

Now you can easily identify the professor’s first and last name and the student’s first and last name.

Equi Join

The equijoin is a basic join where the join condition is equality. The comparison operator used in the join condition is ‘=’.

SELECT *
FROM Table 1
JOIN Table 2
ON (join_conditon is equality);

Consider that we have two relations employee(emp_id, designation, dept) and student(dept_name, dept).

Here the common attribute on which these relations can be joined is dept. so let us apply equijoin on these two relations

SELECT *
FROM employee AS E
JOIN department AS D
ON (E.dept = D.dept);

The result relation for this query is as below:

Theta Join

Theta join is a join where the join condition is other than equality. If the join condition is equality then it comes out to be an equijoin. The join condition in theta joins include the comparison operators such as >, <, >=, <=.

To illustrate theta join, just let us take an example. Consider we have two relations customer and product.

Now if we have to display the tuples from both the tables where the credit limit is greater than the products price then the query will be:

SELECT *
FROM customer
JOIN product
ON (customer.credit_limit > product.price);

Semi Join

Semi join is the join that displays tuples of only the first relation that matches at least one tuple of the other relation. Let us discuss this with an example.

Consider the above relations employee and department and apply the semi-join on employee and department.

SELECT *
FROM employee AS E
SEMI JOIN department AS D
ON (E.dept = D.dept);

The result relation of this query is shown below.

You can notice that only those tuples of employee relation appear in the result relation that has a matching value for common column in department relation.

Outer Join

The outer join is the join in which matched tuples retain in the result relation and the unmatched tuples in the tables are left null in the result relation. Now the outer join is of three forms:

Full outer join: The full outer join is the join where the tuples from both the relations that have matching values in common columns of the relations on which join is performed, appears in the result relation. And the unmatched tuples from both the relations also appear in the result relation.

Let us understand the full outer join with the help of an example:

SELECT *
FROM student
FULL OUTER JOIN professor
ON student.prof_id = professor.prof_id;

You can notice that the tuples from the student relation that does not have matching values in the common columns of professor relation appear in result relation. And the tuples from the professor relation that does not have matching values in the common columns of student relation also appears in result relation.

Left outer join: In the left outer join the tuples that have matching values in common columns of both the relation appear in the result relation. And the unmatched tuples from the left table also appear in the result relation.

Let us understand the left outer join with the help of an example:

SELECT *
FROM student
LEFT OUTER JOIN professor
ON student.prof_id = professor.prof_id;

You can notice that the tuples from the student relation that does not have matching values in the common columns of professor relation appear in result relation.

Right outer join: In the right outer join the tuples that have matching values in the common columns of both the relations appear in the result relation. And the unmatched tuples from the right relation also appear in the result relation.

Let us understand the right outer join with the help of an example:

SELECT *
FROM student
RIGHT OUTER JOIN professor
ON student.prof_id = professor.prof_id;

The tuples from the professor relation that does not have matching values in the common columns of student relation appear in the result relation.

So, this is all about join and their types. We have discussed all kinds of join with an example that makes understanding joins easier.