Joins in DBMS

Join is an operation in DBMS(Database Management System) that combines the rows of two or more tables based on related columns between them. The main purpose of join is to retrieve the data from multiple tables in other words Join is used to perform multi-table queries. It is denoted by ⨝.

Syntax

R3 <- ⨝^(R1) <join_condition> ^(R2)

where R1 and R2 are two relations to be joined and R3 is a relation that will hold the result of the join operation.

Example

Temp <- ⨝^(student) _{S.roll=E.roll}^(Exam)

where S and E are aliases of the student and exam respectively.

JOIN Example

Consider the two tables below as follows:

Both these tables are connected by one common key (column) i.e. ROLL_NO.

We can perform a JOIN operation using the given relational algebra:

Student ⨝ Student_course

Output:

Types of Join

There are many types of Joins in SQL. Depending on the use case, you can use different types of SQL JOIN clauses. Here are the frequently used SQL JOIN types:

1. Inner Join

Inner Join is a join operation in DBMS that combines two or more tables based on related columns and returns only rows that have matching values among tables. Inner join has two types.

• Conditional join
• Equi Join
• Natural Join

(a) Conditional Join

Conditional join or Theta join is a type of inner join in which tables are combined based on the specified condition.

In conditional join, the join condition can include <, >, <=, >=, ≠ operators in addition to the '=' operator.

Example: Suppose two tables A and B

Table A

Table B

A ⨝ _S<T B

Output

Explanation: This query joins the table A, B and projects attributes R, S, T, U were the condition S < T is satisfied.

(b) Equi Join

Equi Join is a type of inner join where the join condition uses the equality operator ('=') between columns.

Example: Suppose there are two tables Table A and Table C

Table A

Table C

A ⨝ _{A.Column B = C.Column B} (C)

Output

Explanation: The data value "a" is available in both tables Hence we write that "a" is the table in the given output.

(c) Natural Join

Natural join is a type of inner join in which we do not need any comparison operators. In natural join, columns should have the same name and domain. There should be at least one common attribute between the two tables.

Example: Suppose there are two tables Table A and Table B

Table A

Table B

A ⨝ B

Output

Explanation - Column Number is available in both tables Hence we write the "Number column once " after combining both tables.

2. Outer Join

Outer join is a type of join that retrieves matching as well as non-matching records from related tables. There are three types of outer join

• Left outer join
• Right outer join
• Full outer join

(a) Left Outer Join

It is also called left join. This type of outer join retrieves all records from the left table and retrieves matching records from the right table.

Example: Suppose there are two tables Table A and Table B

Table A

Table B

A ⟕ B

Output

Explanation: Since we know in the left outer join we take all the columns from the left table (Here Table A) In the table A we can see that there is no Cube value for number 4. so we mark this as NULL.

(b) Right Outer Join

It is also called a right join. This type of outer join retrieves all records from the right table and retrieves matching records from the left table. And for the record which doesn't lies in Left table will be marked as NULL in result Set.

Example: Suppose there are two tables Table A and Table B

A ⟖ B

Output:

Explanation: Since we know in the right outer join we take all the columns from the right table (Here Table B) In table A we can see that there is no square value for number 5. So we mark this as NULL.

(c) Full Outer Join

FULL JOIN creates the result set by combining the results of both LEFT JOIN and RIGHT JOIN. The result set will contain all the rows from both tables. For the rows for which there is no matching, the result set will contain NULL values.

Example: Table A and Table B are the same as in the left outer join

A ⟗ B

Output:

Explanation: Since we know in full outer join we take all the columns from both tables (Here Table A and Table B) In the table A and Table B we can see that there is no Cube value for number 4 and No Square value for 5 so we mark this as NULL.