STUDENTS 3K

  • Home
  • Students+
    • Engineering Students
    • Education Loan
    • Study Abroad Guide
    • Projects Download
  • Freshers
    • Aptitude Q & A
    • Placement Papers
    • Verbal Ability
    • Interview Questions
    • IT Company Details
    • Job Updates
  • Study Resources
    • Career Guidance
    • LAB Programs
      • C Programs
      • CPP Programs [C++]
      • Java Programs
    • Question Papers
    • Learn English
    • Notice Board
  • More –>>
    • Love calculator
You are here: Home / Interview / Difference Between Inner and Outer Join

Difference Between Inner and Outer Join

Joins in SQL table are used to combine the data present in two different tables and display the result in a single table. The table which displays the result of join is temporary; the resultant  table will be based on the comparison of columns between the tables. The effectiveness of joins are based on the predicate; predicate can be defined as the condition used to join the two tables.

SQL: Inner vs Outer join

Example for Join

Consider that we have two tables  which contains employee names and designation

Employee

Emps id Emps Name
1001 Tomb
1002 Smirthi
1003 Benga
1004 Anegan
1005 Saranya
1006 Alex Parrish

Designation

Emps id Emps Design
1001 Team leader
1002 Programmer
1003 Project Leader
1004 Op Manager
1005 C)O
1006 CEO

You may have noted that the designation of the employee, Alex Parrish with the emp id no 106 is not named in the designation table. Also the employee who has the id number 107 is not named in the employee table.

Full Join

The SQL for the full join between two tables is given below.

select * from Employee full join Designation
on Employee.Emp id = Designation.Emp id;

Full join applied for the above two tables will match the rows and join the Employee.Emp id = Designation.Emp id predicate. The rows which don’t have any corresponding value are returned with NULL record. Since all rows are used during this join it is called as Full Join. The resultant full join table is given below.

Employee.Emp id Employee.Emp Name
1001 Tomb
1002 Smirthi
1003 Benga
1004 Angegan
1005 Saranya
1006 Alex Parrish
NULL NULL
Designation.Emp id Designation.Emp Design
1001 Team leader
1002 Programmer
1003 Project Leader
1004 Op Manager
1005 COO
NULL NULL
1006 CEO

Full join is also known as outer join and it can be further classified into left outer join and right outer join.

  • What is Meant by Inner and Outer Joins?

 

What is Inner Join?

Inner join can be used to return the matching rows between the two tables. The following SQL can be used to inner join the two example tables.

select * from employee inner join location on
Employee.Emp id = Designation.Emp id

The resultant table for the above inner join SQL is given below.

Employee.Emp id Employee.Emp Name Designation.Emp id Designation.Emp Design
1001 Tomb 1001 Team Leader
1002 Smirthi 1002 Programmer
1003 Benga 1003 Project Leader
1004 Angegan 1004 Op Manager
1005 Saranya 1005 CFO

The SQL for inner join applied using WHERE clause is given below

select * from Employee1, Designation
where Employee1.Emps id = Designation.Emps id

  • Learn: Aptitude questions and answers

Difference between Inner and Outer Join

  • Inner Join select matching rows between two tables. Outer Join select all the rows and records without value are declared as NULL.
  • Inner Join uses the rows present in the table to match records between two tables
  • Outer Join uses the columns present in the table to match between two tables.
  • Various types of keywords are used in outer joins while inner join doesn’t use any keywords to match the records between the tables.
  • Every row present in two tables will be displayed in the resultant outer join table. The resultant table of inner join contains rows which has matching records.
  • The resultant table of inner join will display only one record for every matching record. Outer join resultant table will return all the records present in both the tables. The records without corresponding value in outer join table will be returned with NULL value.
  • Inner joins are used find the similarities between two tables while outer joins are used to find the differences between the tables.

Filed Under: Interview Tagged With: Join, SQL





Random Materials :

  • Data Mining and Data Warehousing
  • Difference Between Class and Primitive Types
  • Database Roles in SQL
  • CASE Expression in SQL
  • Concurrent Update Problem

Follow Us on Facebook

Advertisement

Company Profile

  • About Students3k
  • Contact Us
  • Earn Money Online
  • Website Archive Page
  • Privacy Policy
  • Disclaimer

Categories

  • Important Programs
  • Study Abroad
  • IT Companies
  • Career Guidance for Students
  • Teachers
  • Verbal Analogies

We Are Social

  • Facebook Fans
  • Twitter Follow
  • Google Plus
  • Pinterest Page

© Copyright 2012 - 2016 Students3k.com · All Rights Reserved · Designed By Benefits

Show