SQL LEFT JOIN - RemoveLoad

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