Showing posts with label Difference between Inner join and Left join. Show all posts
Showing posts with label Difference between Inner join and Left join. Show all posts

Sunday, November 29, 2015

Difference between Inner join and Left join

SQL Join is used for combining the rows between two or more tables using common fields/columns between these tables in a relational database. The goal of joining table is to extract the meaningful list of data. Joins will be obtained from the term called predicate. “Join” is a term used to combine fields of two or more tables by using common values between the tables.
There are different types of joins in SQL and they are –
  • INNER JOIN
  • CROSS JOIN
  • SELF JOIN
  • LEFT OUTER JOIN
  • RIGHT OUTER JOIN
  • FULL OUTER JOIN

Difference between Inner Join vs Left join

For demonstration purpose let’s create tables and let’s call it as – “tClass” and “tStudent”
tClass table structure
Table Class
tStudent table structure
Table Student

Inner join

Inner join
Inner join matches the common records in two tables joined. Here in the above example we have “ClassID” in tclass table and “ClassID” in tstudent table. Below query is used to fetch the list of students which are in Class “10th”
SELECT s.StudentID, s.StudentName
FROM tClass c
INNER JOIN tstudent s ON c.ClassID = s.ClassID
WHERE c.ClassID = 10
So above query is used to fetch the list of students which are studying in Class – “10th”. So here in “tstudent” table all students are stored irrespective of their classes and similarly in “tclass” table all classes are stored. So as the result of the above query, matched records between tstudent and tclass tables are searched and displayed. In the introduction part there was an introduction to the term called “predicate”, in the above query it is – “ON c.ClassID = s.ClassID”, which is one of the essential part of the join.
Note : There are multiple keys associated to each table like in tStudent table have “StudentID” as a primary key field and “ClassID” as a foreign key which inturn refer to the table – “tClass”.
  • In Inner join each record of table A Is matched with each record of Table B and the matched records are then be displayed in the resultant table.
  • JOIN word can be used instead of INNER JOIN, both meant the same.
  • INNER JOIN is the intersection of data between table A and table B.
The above query can be rewritten without using inner join like below but the performance will be impacted compared to inner join –
SELECT s.StudentID, s.StudentName
FROM tClass c , tstudent s
WHERE c.ClassID = s.ClassID AND c.ClassID = 10

Left Join

Left Join
  • Left join will return all the rows from the left table and matching rows from right table.
  • In case of no match with right side table it will return NULL value.
  • “Left Join” and “Left Outer Join” are used interchangeably because records which are returned will be the same with either of these.