LEFT JOIN

Left outer join is also called as left join. Left join return all the rows in the left table, even there is not matching rows in the right table.have two tables (employee and employee_info).

 

I am going to use following sample data.

CREATE TABLE employee(

  id int PRIMARY KEY,

  firstName VARCHAR(20),

  lastName VARCHAR(20),

  salary DECIMAL(10, 2)

);

INSERT INTO employee VALUES (1, "Hari Krishna", "Gurram", 12345.67);

INSERT INTO employee VALUES (2, "Rama Devi", "Gurram", 1234578.67);

INSERT INTO employee VALUES (3, "Lakshmana", "Rao", 9876543.67);

INSERT INTO employee VALUES (4, "Rama", "Krishna", 1234587.67);

INSERT INTO employee VALUES (5, "Sowmya", "asd", 1238745.67);

INSERT INTO employee VALUES (6, "Jyotsna", "PS", 76543.67);

INSERT INTO employee VALUES (7, "Gireesh", "Amara", 87698);

INSERT INTO employee VALUES (8, "Sravani", "Nidamanuri", 987654);

INSERT INTO employee VALUES (9, "Saranya", "Amara", 1987654);

CREATE TABLE employee_info(

  id int PRIMARY KEY,

  city VARCHAR(20),

  state VARCHAR(20),

  country VARCHAR(20)

);

INSERT INTO employee_info VALUES (1, "Bangalore", "Karnataka", "India");

INSERT INTO employee_info VALUES (2, "Chirala", "Andhra Pradesh", "India");

INSERT INTO employee_info VALUES (3, "Punuru", "Andhra Pradesh", "India");

INSERT INTO employee_info VALUES (4, "Bangalore", "Karnataka", "India");

INSERT INTO employee_info VALUES (5, "Bangalore", "Karnataka", "India");

INSERT INTO employee_info VALUES (6, "Nellore", "Andhra Pradesh", "India");

INSERT INTO employee_info VALUES (7, "Chennai", "Tamilnadu", "India");

mysql> SELECT * FROM employee;
+----+--------------+------------+------------+
| id | firstName    | lastName   | salary     |
+----+--------------+------------+------------+
|  1 | Hari Krishna | Gurram     |   12345.67 |
|  2 | Rama Devi    | Gurram     | 1234578.67 |
|  3 | Lakshmana    | Rao        | 9876543.67 |
|  4 | Rama         | Krishna    | 1234587.67 |
|  5 | Sowmya       | asd        | 1238745.67 |
|  6 | Jyotsna      | PS         |   76543.67 |
|  7 | Gireesh      | Amara      |   87698.00 |
|  8 | Sravani      | Nidamanuri |  987654.00 |
|  9 | Saranya      | Amara      | 1987654.00 |
+----+--------------+------------+------------+
9 rows in set (0.00 sec)

mysql> SELECT * FROM employee_info;
+----+-----------+----------------+---------+
| id | city      | state          | country |
+----+-----------+----------------+---------+
|  1 | Bangalore | Karnataka      | India   |
|  2 | Chirala   | Andhra Pradesh | India   |
|  3 | Punuru    | Andhra Pradesh | India   |
|  4 | Bangalore | Karnataka      | India   |
|  5 | Bangalore | Karnataka      | India   |
|  6 | Nellore   | Andhra Pradesh | India   |
|  7 | Chennai   | Tamilnadu      | India   |
+----+-----------+----------------+---------+
7 rows in set (0.00 sec)

Let me get all employees and addresses using JOIN clause.

SELECT *

FROM employee AS emp

JOIN employee_info AS empInfo

ON emp.id = empInfo.id;

mysql> SELECT *
    -> FROM employee AS emp
    -> JOIN employee_info AS empInfo
    -> ON emp.id = empInfo.id;
+----+--------------+----------+------------+----+-----------+----------------+---------+
| id | firstName    | lastName | salary     | id | city      | state          | country |
+----+--------------+----------+------------+----+-----------+----------------+---------+
|  1 | Hari Krishna | Gurram   |   12345.67 |  1 | Bangalore | Karnataka      | India   |
|  2 | Rama Devi    | Gurram   | 1234578.67 |  2 | Chirala   | Andhra Pradesh | India   |
|  3 | Lakshmana    | Rao      | 9876543.67 |  3 | Punuru    | Andhra Pradesh | India   |
|  4 | Rama         | Krishna  | 1234587.67 |  4 | Bangalore | Karnataka      | India   |
|  5 | Sowmya       | asd      | 1238745.67 |  5 | Bangalore | Karnataka      | India   |
|  6 | Jyotsna      | PS       |   76543.67 |  6 | Nellore   | Andhra Pradesh | India   |
|  7 | Gireesh      | Amara    |   87698.00 |  7 | Chennai   | Tamilnadu      | India   |
+----+--------------+----------+------------+----+-----------+----------------+---------+
7 rows in set (0.00 sec)

As you see above output, Employees with ids 8 and 9 not present in the result, it is because they are not exist in employee_info table. By using LEFT JOIN, you can still get the data, even though employee_info table don’t have any entries.

SELECT *
FROM employee AS emp
LEFT JOIN employee_info AS empInfo
ON emp.id = empInfo.id;

mysql> SELECT *
    -> FROM employee AS emp
    -> LEFT JOIN employee_info AS empInfo
    -> ON emp.id = empInfo.id;
+----+--------------+------------+------------+------+-----------+----------------+---------+
| id | firstName    | lastName   | salary     | id   | city      | state          | country |
+----+--------------+------------+------------+------+-----------+----------------+---------+
|  1 | Hari Krishna | Gurram     |   12345.67 |    1 | Bangalore | Karnataka      | India   |
|  2 | Rama Devi    | Gurram     | 1234578.67 |    2 | Chirala   | Andhra Pradesh | India   |
|  3 | Lakshmana    | Rao        | 9876543.67 |    3 | Punuru    | Andhra Pradesh | India   |
|  4 | Rama         | Krishna    | 1234587.67 |    4 | Bangalore | Karnataka      | India   |
|  5 | Sowmya       | asd        | 1238745.67 |    5 | Bangalore | Karnataka      | India   |
|  6 | Jyotsna      | PS         |   76543.67 |    6 | Nellore   | Andhra Pradesh | India   |
|  7 | Gireesh      | Amara      |   87698.00 |    7 | Chennai   | Tamilnadu      | India   |
|  8 | Sravani      | Nidamanuri |  987654.00 | NULL | NULL      | NULL           | NULL    |
|  9 | Saranya      | Amara      | 1987654.00 | NULL | NULL      | NULL           | NULL    |
+----+--------------+------------+------------+------+-----------+----------------+---------+
9 rows in set (0.00 sec)

Even we do not have entries for employee id 8 and 9 in employee_info table, we can still get these recrods from left table.

Previous                                                 Next                                                 Home