DBMS Joins

Facebooktwitterredditpinterestlinkedinmailby feather

Joins are the combination of related tuples from the two different relations or tables into a single type. It is similar to the Cartesian product except the fact that in Cartesian product operation, a user gets all the possible combinations of relations. While in joins operation use

r gets only those combinations that satisfy some matching conditions between two relations. A relation can also join to itself, which is called as a self-join.

Join operation combines two tuples from different relations if and only if the following conditions are satisfied:

  • There must be a common attribute in both the relation.
  • Join condition must be satisfied.

There are following different type of joins:

  1. Inner Join or Equi Join
  2. Outer Join
  3. Natural join
  4. Self-join

1. Inner Join or Equi Join

Inner Join or Equi Join is represented by (=) equal sign and displays all the records which are common between two relations. If no record is selected, return NULL. The query of Inner join compares each tuple of Relation1 with each tuple of Relation2 to find all pairs of rows which satisfy the join condition. When the join condition is satisfied, attribute values for each matched pair of tuples of A and B are combined into a resultant tuple.

DBMS Joins

Syntax:

Relation1.attribute_name = Relation2.attribute_name

Example:

Table: student

Student_id Name Age
101 RS 20
102 TK 21
103 Sk 20

Table: Course

Course_id Student_id Course_name
401 101 B.tech
402 102 MCA
403 103 MCA

Query: Display the name of student who study in each course

  • Π Name(student.Student_id =Course.Student_id)(student x course))

Output:

Name
RS
TK
Sk

2. Outer Join

The Outer Join displays all records from both the participating relation which satisfy the join condition along with tuples which do not satisfy the join condition.

An outer join is mainly of three types:

  1. Left Outer join
  2. Right Outer join
  3. Full Outer join

Left Outer Join

The left outer join is represented by () symbol and displays all the tuples from the left relation and the matching tuples from the right relation. If there is exists no matching record in the right relation, it displays the NULL value.

DBMS Joins 1

Syntax:

Relation1 Relation2

Example:

Table: student

Student_id Name Age
101 RS 20
102 TK 21
103 Sk 20
104 Pk 21

Table: Course

Course_id Student_id Course_name
401 101 B.tech
402 102 MCA
403 103 MCA
  • (Student Course)

Output:

Student_id Name Age Course_id Course_name
101 RS 20 401 B.tech
102 TK 21 402 MCA
103 Sk 20 403 MCA
104 Pk 21 NULL NULL

Right Outer Join

The right outer join is represented by () symbol and displays all the tuples from the right relation and the matching tuples from the left relation. If there is exists no matching record in the left relation, it displays the NULL value.

DBMS Joins 2

Syntax:

Relation1 Relation2

Example:

Table: student

Student_id Name Age
101 RS 20
102 TK 21
103 Sk 20

Table: Course

Course_id Student_id Course_name
401 101 B.tech
402 102 MCA
403 103 MCA
404   MBA
  • (student Course)

Output:

Student_id Name Age Course_id Course_name
101 RS 20 401 B.tech
102 TK 21 402 MCA
103 Sk 20 403 MCA
NULL NULL NULL 404 MBA

Full Outer Join

The full outer join is represented by() symbol and combines the results of both right and left outer joins and returns all (matching or unmatching) records from both the relation.

DBMS Joins 3

Syntax:

Relation1 Relation2

Example:

Table: student

Student_id Name Age
101 RS 20
102 TK 21
103 Sk 20

Table: Course

Course_id Student_id Course_name
401 101 B.tech
402 102 MCA
404   MBA
  • (student Course)

Output:

Student_id Name Age Course_id Course_name
101 RS 20 401 B.tech
102 TK 21 402 MCA
103 Sk 20 NULL NULL
NULL NULL NULL 404 MBA

3. Natural Join

Natural Join is represented by a (⋈) symbol, and it is a type of Inner join which is based on attributes having the same name and datatype present in both the relations to get joined.

Difference between Inner join and Natural join

  • Inan inner join, a user has to specify a join condition to join the two relations. Whereas in the natural join, a user doesn’t specify a join condition. Users just write the two relation names without any condition. Then the natural join will automatically check for equality between the records for every column existing in both relation.
DBMS Joins 4

Syntax:

Relation1 Relation2

Example:

Table: student

Student_id Name Age
101 RS 20
102 TK 21
103 Sk 20

Table: Course

Course_id Student_id Course_name
401 101 B.tech
402 102 MCA
  • (student Course)

Output:

Student_id Name Age Course_id Course_name
101 RS 20 401 B.tech
102 TK 21 402 MCA

4. Self-Join

A self-join is a join in which a relation is joined with itself (which is also known as Unary relationship), especially when the relation has a foreign key which references to its own primary key. To join a relation to itself means that each tuple of the relation is combined with itself and with every other tuple of the relation. It can be viewed as a join of two copies of the same relation.

DBMS Joins 5

Example:

Table: student

student_id Name Course Class_representative
101 RS MCA 101
102 TK MBA NULL
103 Sk MCA 101
104 Pk MBA NULL

Query: Display the name of student who are class representative

  • Π a.student_id, a.Name,b.Course, b.Namea.student_id = b.Class_representative) (a.student x b.student))

Output:

student_id Name Course name
101 RS MCA RS
101 RS MCA SK
Facebooktwitterredditpinterestlinkedinmailby feather