Difference between INNER and LEFT join

Difference between INNER and LEFT join

May 17, 2019 DATAcated Challenge 0

In Database development Joins are commonly used key word. Joins generally do mapping between two or more tables and retrieve a meaningful information from multiple tables. There are Inner, Left outer, Right Outer and Full Outer joins available. All this joins has different functionality.

INNER JOIN: Inner join can be used simply by typing JOIN as well. This is the join which will be used when required to get matched data of two or more table. For Instance, I have one table which has Employee Table and there is other table which has information of Sales. Sales table contains Employee_id with each sales record. So if required to get information that in last month which employee did highest sales. So here we need to get Employee details such as name. for that we will need to apply inner join with sales table.

Eg Syntex: Employee E Inner join Sales S on E.Employee_id = S.Employee_id

LEFT JOIN: Key work like Left outer join and left join are same. This join is used when required to get Full left side table but matched data of right tables. with using this Join it can be exclude matched data by using IS NULL with right table column. Below is the example query which will return Employee who does not have any sale in last Month.

eg: select distinct E.Employee_name from Employee E left join Sales S on S.Employee_id = E.Employee_id where S.Sale_date between ‘2019-04-01’ and ‘2019-05-01’ and S.Employee_id IS NULL

One more difference between INNER and LEFT join is INDEX can not be created on view which has LEFT JOIN in view query.

By. Sanjay Donda


Leave a Reply

Your email address will not be published. Required fields are marked *