LEFT JOIN is used between two tables. LEFT JOIN returns all records from the left table and matches to right table, if the records do not match in the right table then show NULL value.
LEFT JOIN is also called a LEFT OUTER JOIN.
For SELECT Statement
Syntax:-
SELECT table_name1.column_name(s), table_name2.column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
Example:- Suppose we have two table employees and emp_salary.
employees Table:- this table has employees records.
+----+------------+-----------+----------------+-----------+
| id | first_name | last_name | email | country |
+----+------------+-----------+----------------+-----------+
| 1 | John | Tailor | john@abc.com | USA |
| 2 | Rom | Tailor | rom@abc.com | USA |
| 3 | Andrew | Symonds | andrew@abc.com | Australia |
| 4 | Miacle | Tailor | miacle@abc.com | Australia |
| 5 | Sachin | Tendulkar | sachin@abc.com | India |
| 6 | Virat | Kohli | virat@abc.com | India |
| 7 | rohit | NULL | rohit@abc.com | India |
emp_salary table:- this table has employee’s salary details.
+----+--------+--------+
| id | emp_id | salary |
+----+--------+--------+
| 1 | 5 | 200000 |
| 2 | 6 | 180000 |
+----+--------+--------+
Find all the employees details with salary.
Query:-
SELECT employees.id,employees.first_name,employees.last_name,
employees.email,employees.country,emp_salary.salary
FROM employees
LEFT JOIN emp_salary
On employees.id=emp_salary.emp_id
Output:-
+----+------------+-----------+----------------+-----------+--------+
| id | first_name | last_name | email | country | salary |
+----+------------+-----------+----------------+-----------+--------+
| 5 | Sachin | Tendulkar | sachin@abc.com | India | 200000 |
| 6 | Virat | Kohli | virat@abc.com | India | 180000 |
| 1 | John | Tailor | john@abc.com | USA | NULL |
| 2 | Rom | Tailor | rom@abc.com | USA | NULL |
| 3 | Andrew | Symonds | andrew@abc.com | Australia | NULL |
| 4 | Miacle | Tailor | miacle@abc.com | Australia | NULL |
| 7 | rohit | NULL | rohit@abc.com | India | NULL |
+----+------------+-----------+----------------+-----------+--------+