Scribd
Upload
4 views9 pages

JOIN SQL

The document explains various types of join operations in relational databases, including Theta Join, Natural Join, and Outer Joins (Left, Right, and Full). Each join type is defined with examples illustrating how tuples from different relations are combined based on specific conditions. The document emphasizes the inclusion of tuples from participating relations in the resulting relations for outer joins.

Uploaded by

Niranjan Uvaraja
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
4 views9 pages

JOIN SQL

The document explains various types of join operations in relational databases, including Theta Join, Natural Join, and Outer Joins (Left, Right, and Full). Each join type is defined with examples illustrating how tuples from different relations are combined based on specific conditions. The document emphasizes the inclusion of tuples from participating relations in the resulting relations for outer joins.

Uploaded by

Niranjan Uvaraja
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

JOIN

● JOIN is a combination of Cartesian product followed by a selection process.

● A join operation pairs two tuples from different relations, if a given join

condition is satisfies
Theta (Θ) Join

● Continues tuples from different relations provided , they satisfy the theta

condition
● The join condition is denoted by θ

● R1 ⨝Θ R2
EXAMPLE

STUDENT ⨝[Link] = [Link] SUBJECT


S_ID NAME STD CLASS STUDENT

101 ALEX 10 10 MAT

101 ALEX 10 10 ENG

102 MARIA 10 10 MUSIC

102 MARIA 10 10 SPORTS


Natural Join (⨝)

● It does not use any comparison operation

● We can perform a natural join only if there is at least one common attribute

that exists between a relation


EXAMPLE

COURSE HOD Course ⨝ HOD


C_ID COURSE DEPT DEPT HEAD C_ID COURSE DEPT HEAD
CS ALEX
CS01 DATABASE CS CS01 DATABASE CS ALEX
ME MARIA

ME01 MECHANICS ME EE MAYA ME01 MECHANICS ME MARIA

EE01 ELECTRONI EE EE01 ELECTRONICS EE MAYA


CS
Outer Join

● Outer join includes the tuples from the participating relations in the resulting

relation.
Left outer join (R⟕S )

● All the tuples from the left relation R , and matching rows in S , are included in

the relating relation

COURSE HOD Course ⟕ HOD

A B A B A B C D

100 DATABASE 100 ALEX 100 DATABASE 100 ALEX

101 MECHANICS 101 MARIYA 101 MECHANICS -- --

102 ELECTRONICS 102 MAYA 102 ELECTRONICS 102 MARIYA


Right outer join ( R⟖S )

● All the tuples from the right relation, S and matching rows from R are included

in the resulting relation

COURSE HOD Course ⟖ HOD

A B A B A B C D

100 DATABASE 100 ALEX 100 DATABASE 100 ALEX

101 MECHANICS 101 MARIYA 102 ELECTRONICS 102 MARIYA

102 ELECTRONICS 102 MAYA -- -- 104 MAYA


Full outer join ( R⟗S )

● All the tuples from both participating relations are included in the resulting

relation.

COURSE HOD Course ⟗ HOD

A B A B A B C D

100 DATABASE 100 ALEX 100 DATABASE 100 ALEX

101 MECHANICS 101 MARIYA 101 MECHANICS -- --

102 ELECTRONICS 102 MAYA 102 ELECTRONICS 102 MARIYA

-- -- 104 MAYA

You might also like