Hi all,
What’s is the bifference betweeen left join and inner join. In this tutorial I will explain inner join and left join.First we discuss why we use join or what are the purpose of join in MYSQL.
Why we use Join ?
The main purpose of SQL joins are, it is used for combine two or more table based on common field cinditions.
In other words, if you want to retrieve data for more than one tables in that case we need to use join.
Now let’s check the difference between inner join and left join.
Difference Between Left Join and Inner Join
To understand the concept of left join and inner join, create two tables Employee1 and Employee2. Insert some values.
create table Employee1 ( EMPLOYEE_ID int(10) NOT NULL AUTO_INCREMENT, EMPLOYEE_NAME varchar(200), PRIMARY KEY(EMPLOYEE_ID) ); insert into Employee1 (EMPLOYEE_ID,EMPLOYEE_NAME) values(1,'Jose'); insert into Employee1 (EMPLOYEE_ID,EMPLOYEE_NAME) values(1,'Thomas'); insert into Employee1 (EMPLOYEE_ID,EMPLOYEE_NAME) values(1,'Joseph'); +-----------+-----------------+ EMPLOYEE_ID| EMPLOYEE_NAME | +-----------+-----------------+ | 1 | Jose | | 2 | Thomas | | 3 | Joseph | +-----------+-----------------+
Create second table Employee2
create table Employee2( EMPLOYEE_ID int(10) NOT NULL AUTO_INCREMENT, SALARY varchar(100), PRIMARY KEY(EMPLOYEE_ID) ); insert into Employee2(EMPLOYEE_ID, SALARY) values(1 ,4000); insert into Employee2(EMPLOYEE_ID, SALARY) values(2 ,8000); +-----------+----------+ EMPLOYEE_ID| SALARY | +-----------+----------+ | 1 | 4000 | | 2 | 8000 | +-----------+----------+
Now both Employee1 and Employee2 the tables are created.
Inner Join
The INNER JOIN selects all rows from both tablesresults based on the match between the columns in both tables. Returns all the rows when there is atleast one matches in both tables.
Inner JOIN query will be,
SELECT * from Employee1, Employee2 where Employee1.Employee_ID = Employee2.Employee_ID;
The result will be
Employee_ID | Employee_Name | Employee_ID | Salary |
---|---|---|---|
1 | Jose | 1 | 4000 |
2 | Thomas | 2 | 8000 |
Left Join
LEFT JOIN query will be,
SELECT Employee_ID, Employee_Name,Salary
FROM Employee1
LEFT JOIN Employee2
ON Employee1.Employee_ID = Employee2.Employee_ID;
The result will be
Employee_ID | Employee_Name | Salary |
---|---|---|
1 | Jose | 4000 |
2 | Thomas | 8000 |
3 | Joseph | NULL |
The LEFT JOIN keyword returns all rows from the left table (Employee1), with the matching rows in the right table (Employee2).
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.