
Quick Contact
DBMS Tutorial
- DBMS Tutorial
- What is Database Management System (DBMS)?
- Components of DBMS
- Applications of DBMS
- Three Schema DBMS Architecture
- Difference between DBMS and RDBMS?
- Difference between File Oriented System and DBMS
- Types of Data Models
- DBMS Schema and Instances
- Data Independence and Data Abstraction
- Database Users and Administrator
- DBMS Languages and Interfaces
DBMS ER Model
DBMS Relational Data Model
DBMS Normalization
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
- Inner Joins
- 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