SQL-LEFT OUTER JOIN

<< Previous - INNER Join

Next - RIGHT OUTER Join >>

What is Left join in SQL?

• The LEFT OUTER JOIN returns the result set that includes all the rows from the first table specified on the left side of the join clause and the rows from the matching column of the right-side table.
• Sometimes, if there is no matching column on the right-side table, still the join will return the rows from the left table and null from the right table.
• The keyword LEFT OUTER JOINis same as LEFT JOIN. We can use both keywords alternatively to perform the SQL left join.

Syntax

SELECT Table1.column-name1 Table1.column-name2  Table2.column-name 2......
FROM Table-name1
LEFT JOIN Table-name2
ON Table1.column =  Table2.column;

Example with LEFT JOIN

Consider the demo tables that are given below:

1. Student_Personal Table:

   
   +--------+---------+------+-----------+
   | RollNo | Name    | Age  | City      |
   +--------+---------+------+-----------+
   |      1 | Aruna   |   18 | Chennai   |
   |      2 | Varun   |   19 | Bangalore |
   |      3 | Ara     |   19 | Kerala    |
   |      4 | Markdin |   18 | Mumbai    |
   |      5 | Kannan  |   20 | Kerala    |
   |      6 | Kanika  |   18 | Chennai   |
   |      7 | Jose    |   19 | Kerala    |
   |     10 | Sreeja  |   20 | Bangalore |
   +--------+---------+------+-----------+
   

2. Student_ID Table:

   
   +--------+-------+---------+------+
   | RollNo | ID_NO | Name    | Dept |
   +--------+-------+---------+------+
   |      1 |   101 | Arun    | CSE  |
   |      2 |   102 | Varun   | IT   |
   |      3 |   103 | Ara     | ECE  |
   |      4 |   104 | Markdin | ECE  |
   |      5 |   105 | Kannan  | IT   |
   |      6 |   106 | Aruna   | CSE  |
   |      7 |   107 | Jose    | ECE  |
   |      8 |   108 | Jithu   | CSE  |
   |      9 |   109 | Sai     | ECE  |
   +--------+-------+---------+------+
   

Following is the LEFT JOIN query statement used to join these two tables:

mysql> SELECT Student_Personal.Rollno,Student_Personal.Name,Student_ID.ID_NO,Student_ID.Dept
    -> FROM Student_Personal
    -> LEFT JOIN Student_ID
    -> ON student_Personal.RollNo = Student_ID.RollNo;

When you execute this above statement, you would get the following result-set

+--------+---------+-------+------+
| Rollno | Name    | ID_NO | Dept |
+--------+---------+-------+------+
|      1 | Aruna   |   101 | CSE  |
|      2 | Varun   |   102 | IT   |
|      3 | Ara     |   103 | ECE  |
|      4 | Markdin |   104 | ECE  |
|      5 | Kannan  |   105 | IT   |
|      6 | Kanika  |   106 | CSE  |
|      7 | Jose    |   107 | ECE  |
|     10 | Sreeja  |   NULL| NULL |
+--------+---------+-------+------+
8 rows in set (0.00 sec)

<< Previous - INNER Join

Next - RIGHT OUTER Join>>