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 | +----+------------+-----------+----------------+-----------+--------+