Home » DBMS

Inner Join vs Outer Join in DBMS

Inner Join vs Outer Join in DBMS: Here, we are going to learn about the inner join and outer join and what are the differences between inner join and outer join?
Submitted by Anushree Goswami, on June 29, 2019

Consider the given tables:

Student1 –

RollNo Name Marks
1 Ashish 98
2 Shivam 72
3 Tarun 53
4 Yash 89

Student2 –

RollNoNameMarks
1Anjali99
4Dinesh79
5Harsh95
7Kartik88

Inner Join

Inner Join is that type of join which it returns the record that has matching values in both tables. There are mainly three types of join which we have studied about in Extended Operators in Relation Algebra section, i.e., Condition Join, Equi Join, and Natural Join. It is denoted by the symbol .

For e.g. – Select the students from Student1 whose RollNo is equal to the RollNo of Student2 table.

Student1⋈Student1.RollNo = Student2.RollNoStudent2

Syntax:

    SELECT * FROM Student1 
    INNER JOIN Student2 ON Student1.RollNo = Student2.RollNo;

Output

Inner join in DBMS

Outer Join

There are three types of Outer Join,

a) Left Outer Join

Left Outer Join is that type of Join which returns all records from the left table and the matched record from the right table. It is denoted by the symbol .

For e.g. – Select the students from Student1 whose RollNo is greater than the RollNo of Student2 table.

Student1⟕Student1.RollNo>Student2.RollNoStudent2

Syntax:

    SELECT * FROM Student1 
    LEFT OUTER JOIN Student2 ON Student1.RollNo > Student2.RollNo;

Output

Outer join in DBMS

b) Right Outer Join

Right Outer Join is that type of Join which returns all records from the right table and the matched record from the left table. It is denoted by the symbol .

For e.g. – Select the students from Student1 whose RollNo is greater than the RollNo of Student2 table.

Student1⟖Student1.RollNo>Student2.RollNoStudent2

Syntax:

    SELECT * FROM Student1 
    RIGHT OUTER JOIN Student2 ON Student1.RollNo > Student2.RollNo;

Output

Outer join in DBMS

c) Full Outer Join

Full Outer Join is that type of Join which returns all records when there is a match in either the left or right table. It is denoted by the symbol .

For e.g. – Select the students from Student1 whose RollNo is greater than the RollNo of Student2 table.

Student1⟗Student1.RollNo>Student2.RollNoStudent2

Syntax:

    SELECT * FROM Student1 
    FULL OUTER JOIN Student2 ON Student1.RollNo > Student2.RollNo;

Output

Outer join in DBMS

Summary of Inner Join vs Outer Join

Inner Join Outer Join
It returns the record that has a matching value in both tables. It returns the record that has matching value as well as some dissimilar data from the tables.
Types of Inner Join - Condition Join, Equi Join and Natural Join. Types of Outer Join - Left Outer Join, Right Outer Join, and Full Outer Join.
There must be at least one matching data between the two tables. There may not be any matching data between the two tables.
It never produces NULL values. It can produce NULL values.
It will not produce any tuple if no matching data is found between the two tables. It will always produce at least one tuple even if no matching data is found in between the two tables.





Comments and Discussions

Ad: Are you a blogger? Join our Blogging forum.
Learn PCB Designing: PCB DESIGNING TUTORIAL




Languages: » C » C++ » C++ STL » Java » Data Structure » C#.Net » Android » Kotlin » SQL
Web Technologies: » PHP » Python » JavaScript » CSS » Ajax » Node.js » Web programming/HTML
Solved programs: » C » C++ » DS » Java » C#
Aptitude que. & ans.: » C » C++ » Java » DBMS
Interview que. & ans.: » C » Embedded C » Java » SEO » HR
CS Subjects: » CS Basics » O.S. » Networks » DBMS » Embedded Systems » Cloud Computing » Machine learning » CS Organizations » Linux » DOS
More: » Articles » Puzzles » News/Updates

© https://www.includehelp.com some rights reserved.