DBMS Joins

DBMS Joins

DBMS Joins can be simply represented as the connecting or merging the related rows from two different relations into an individual type. It is represented by Bowtie (?). It is necessarily a cartesian product followed by a selection criterion.

Syntax of Join Operation

A?_{< Join Condition>}B

Example

Consider the following relation

EMPLOYEE

EMP-ID	EMP-NAME
111	Rohan
112	Vishal
113	Sumit

SALARY

EMP-ID	SALARY
111	40,000
112	30,000
113	20,000

Operations: (EMPLOYEE?SALARY)

Results

EMP-ID	EMP-NAME	SALARY
111	Rohan	40,000
112	Vishal	30,000
113	Sumit	20,000

Types of Join Operations

There are basically two types of joins

1. Inner Joins
2. Outer Joins

Inner Joins

These joins are the one that has the rows that satisfy some conditions and rest are rejected.

Types of Inner Joins

There are following types of Inner Joins

Theta Join(?)

The general method of the join operation is known as a Theta join. It is expressed by the symbol(?).

Syntax of Theta Join(?)

A?_? B

Theta join can operate any positions in the selection test.

Example

Consider the following table

Table X

Column1	Column2
1	1
1	2

Table Y

Column1	Column2
1	1
1	3

Operations:A?_{X.column2>Y.column2} (B)

Result

Column1	Column2
1	2

EQUI Join(?)

It is also known as Inner Join. It is denoted by (=) equal sign and shows all the data which are frequent between two relations. If no data is chosen, return null. The query of the inner join measures each row of Relation1 with each row of Relation2 to find all combinations of rows that appease the join condition. When the join condition is fulfilled, attribute values for each doubled combination of rows of X and Y are mixed into a resultant row.

Syntax of EQUI Join(?)

Relation1.attribute_name=Relation2.attribute_name

Example: Consider the following relations

Student

Student-id	Name	Age
121	Nisha	19
122	Meera	20
123	Rahul	17

Course

Couse-id	Student-id	Course-Name
221	121	B.tech
222	122	MCA
223	123	MCA

Query: Display the name of the student who studies in each course.

?_Name (s(Student.Student-id=Course.Student-id)(student x course))

Output

Name
Nisha
Meera
Rahul

Natural Join(?)

Natural join is the set of all mergers of rows in R and S that are equal to their common attributes. It is represented by (?) symbol.

Example

Consider the table EMPLOYEE and SALARY.

EMPLOYEE

EMP-ID	EMP-NAME
111	Rohan
112	Vishal
113	Sumit

SALARY

EMP-ID	SALARY
111	40,000
112	30,000
113	20,000

Query: Get the employee name with their salary name.

?_{EMP-NAME,SALARY} (EMPLOYEE?SALARY)

Output

EMP-NAME	SALARY
Rohan	40,000
Vishal	30,000
Sumit	20,000

Conditional Join(?_C)

Conditional join is used when we need to join two or more relations based on some conditions. Example: select students whose Roll-No is higher than EMP-No of employees

Syntax of Conditional Join(?_C)

Syntax of Conditional Join(?_C)

STUDENT?_{C STUDENT.Roll-No>EMPLOYEE.Emp-No} EMPLOYEE

Example

Consider the following relation

STUDENT

Roll-No	Name	Age
111	Mahesh	20
112	Ashutosh	18
113	Neha	18

EMPLOYEE

EMP-No	Name	Age
111	Mahesh	20
112	Ramesh	22
113	Rajesh	23

Query: Get information on those employees and students whose student roll no is more than employee no.

s_{(STUDENT.ROLL-NO>EMPLOYEE.EMP-NO)}(STUDENT X EMPLOYEE)

Output

Roll-No	Name	Age	Emp-No	Name	Age
112	Ashutosh	18	111	Mahesh	20
113	Neha	18	111	Mahesh	20

Outer Join

Outer Join shows all data from both the participating relation which satisfy the join condition along with rows which do not satisfy the join condition.

Types of Outer Join

There are three types of an outer join

Left Outer Join (A B)

The left outer join is denoted by symbol and shows all the rows from the left relation and the duplicate rows from the right relation. And if there is exists no duplicate data in the right relation, it shows the null value.

Syntax of Left Outer Join

A B

Example

Consider the following relation

Customer

Name	Address	Age
Amar	Sec-12	Noida
Rohan	Sec-16	Delhi
Rajesh/td>	Sec-21	Faridabad

Account

Name	Account	Branch
Amar	A-101	Noida
Kumar	A-201	Delhi
Rajesh/td>	A-301	Delhi

Operations: (Customer Account)

Output

Name	Address	City	Account	Branch
Amar	Sec-12	Noida	A-101	Noida
Rohan	Sec-16	Delhi	Null	Null
Rajesh	Sec-21	Faridabad	A-301	Delhi

Right Outer Join (A B)

The right outer join is denoted by symbol and shows all the rows from the right relation and the duplicate rows from the left relation. If there is exists no duplicate data on the left relation, it shows the NULL value.

Syntax of Right Outer Join

A B

Example

Consider the following relation

Customer

Name	Address	Age
Amar	Sec-12	Noida
Rohan	Sec-16	Delhi
Rajesh/td>	Sec-21	Faridabad

Account

Name	Account	Branch
Amar	A-101	Noida
Kumar	A-201	Delhi
Rajesh/td>	A-301	Delhi

Operations: (Customer Account)

Output

Name	Address	City	Account	Branch
Amar	Sec-12	Noida	A-101	Noida
Kumar	Null	Null	A-201	Delhi
Rajesh	Sec-21	Faridabad	A-301	Delhi

Full Outer Join (A B)

The full outer join is denoted by symbol and merge the outcome of both right and left outer joins and returns all matching or un-matching data from both the relation.

Syntax of Full Outer Join

A B

Example

Consider the following relation

Customer

Name	Address	Age
Amar	Sec-12	Noida
Rohan	Sec-16	Delhi
Rajesh/td>	Sec-21	Faridabad

Account

Name	Account	Branch
Amar	A-101	Noida
Kumar	A-201	Delhi
Rajesh/td>	A-301	Delhi

Operations: (Customer Account)

Output

Name	Address	City	Account	Branch
Amar	Sec-12	Noida	A-101	Noida
Rohan	Sec-16	Delhi	Null	Null
Rajesh	Sec-21	Faridabad	A-301	Delhi
Kumar	Null	Null	A-201	Delhi

 

Apply now for Advanced DBMS Course