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 |
+----+------------+-----------+----------------+-----------+--------+
SQL LEFT JOIN – Interview Questions
Q 1: What is LEFT JOIN?
Ans: Returns all records from left table and matching right table records.
Q 2: What if no match exists?
Ans: NULL values are returned.
Q 3: Is LEFT JOIN same as LEFT OUTER JOIN?
Ans: Yes.
Q 4: Can LEFT JOIN use WHERE?
Ans: Yes.
Q 5: Is LEFT JOIN commonly used?
Ans: Yes.
SQL LEFT JOIN – Objective Questions (MCQs)
Q1. What does a LEFT JOIN return?
Q2. If there is no matching record in the right table, LEFT JOIN returns:
Q3. Which table is fully preserved in a LEFT JOIN?
Q4. Which keyword can replace LEFT JOIN in SQL?
Q5. LEFT JOIN is mainly used when: