What’s the difference between inner join and left join in MySql? In relational databases (Such as MySql etc.) concept of join is very important. In this tutorial, I am going to explain inner join and left join with examples.
MySql Joins?
Joins are used when we need to retrieve data from multiple tables. In relational databases, We need to retrieve data from more than one tables in that case joins is very helpful.
When we use Joins in MySql query then it matches rows in one table with rows in another table based on join-predicate (or condition) and gives you the output that contains columns from both or either tables.
I hope you understand the purpose of join and their usage. Now let’s understand the difference between inner join and left join.
How to find second highest salary in MySql
Difference Between Inner Join and Left Join
To understand the concept of inner join and left join, let’s first create two tables (emp_data and emp_salary). Let’s insert some dummy values.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
create table emp_data ( emp_id int(10) NOT NULL AUTO_INCREMENT, name varchar(200), designation varchar(200), PRIMARY KEY(emp_id) ); insert into emp_data (name,designation) values('John','Manager'); insert into emp_data (name,designation) values('smith','Sales Associate'); insert into emp_data (name,designation) values('Amit','Software Engineer'); insert into emp_data (name,designation) values('vikash','Customer Support'); +--------+--------+-------------------+ | emp_id | name | designation | +--------+--------+-------------------+ | 1 | John | Manager | | 2 | smith | Sales Associate | | 3 | Amit | Software Engineer | | 4 | vikash | Customer Support | +--------+--------+-------------------+ |
The first table is created successfully. Now, let’s create a second table emp_salary.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
create table emp_salary ( id int(10) NOT NULL AUTO_INCREMENT, salary int(10), emp_id int(10), PRIMARY KEY(id) ); insert into emp_salary (salary,emp_id) values(20000,4); insert into emp_salary (salary,emp_id) values(15000,1); insert into emp_salary (salary,emp_id) values(25000,6); insert into emp_salary (salary,emp_id) values(18000,9); +----+--------+--------+ | id | salary | emp_id | +----+--------+--------+ | 1 | 20000 | 4 | | 2 | 15000 | 1 | | 3 | 25000 | 6 | | 4 | 18000 | 9 | +----+--------+--------+ |
So both tables are created successfully.
Inner Join in MySql
When we use inner join in a query, it selects all rows from both the tables which are common (or match) based upon the join-predicate (or condition).
How to connect MySql database using PHP
Inner join of table A and B gives all the record that is common in both the tables.
Difference between Inner Join and Left Join
|
1 2 3 4 5 |
select ed.name, ed.designation, es.salary from emp_data ed inner join emp_salary es on ed.emp_id=es.emp_id; Or select ed.name,ed.designation,es.salary from emp_data ed join emp_salary es on ed.emp_id=es.emp_id; |
Output –
|
1 2 3 4 5 6 |
+--------+------------------+--------+ | name | designation | salary | +--------+------------------+--------+ | John | Manager | 15000 | | vikash | Customer Support | 20000 | +--------+------------------+--------+ |
We get common rows between two tables as an output. JOIN is a shorter version of INNER JOIN. There is no difference between join and inner join keyword, they both are equal in performance as well as implementation.
LEFT JOIN in MySql
Left join for tables A and B, gives all records of the left table (which is A) even if the condition does not find any matching record in a second table (which is B). The result is NULL for the right table if there is no match.
Difference Between Inner Join and Left Join
|
1 |
select ed.name, ed.designation, es.salary from emp_data ed left join emp_salary es on ed.emp_id=es.emp_id; |
output –
|
1 2 3 4 5 6 7 8 |
+--------+-------------------+--------+ | name | designation | salary | +--------+-------------------+--------+ | John | Manager | 15000 | | smith | Sales Associate | NULL | | Amit | Software Engineer | NULL | | vikash | Customer Support | 20000 | +--------+-------------------+--------+ |
Let’s look at the output, there is no record for id 2 and 3 (smith and Amit) in the emp_salary table. In that case the NULL value is returned.
How to compare NULL value in MySql
Conclusion
I hope after reading this tutorial, you understand the difference between inner join and left join. If you want to add something, please let us know by your comments.
