SQL Left Join

A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table.

Table: Employee                            
+----+-------+--------+
| Id | FName | LNAME |
+----+-------+--------+
1  | Ram   | Singh  |
2  | Henry | Lal    |
3  | Sam   | Lal    |
4  | Shyam | Singh  |
5  | Geeta | Rani   |
6  | Sheet | Devi   |
7  | Leela | Bati   |
+----+-------+--------+
TableAddress
+----+------------+---------+--------+
| Id | EmployeeID | Country | City   |    
+----+------------+---------+--------+    
1  | 1          | India   | Delhi  |
1  | 2          | India   | Noida  | 
1  | 6          | India   | Delhi  | 
+----+------------+---------+--------+  


Approach: Left or Left Outer join

Mysql


SELECT e.ID as EMPID, e.FName, e.LNAME, a.Country, a.City
FROM Employee e
LEFT JOIN Address a
ON e.Id=a.EmployeeID;

Result : 

EMPID    FNAME    LNAME     Country     City
 1       Ram      Singh     India       Delhi
 2       Henry    Lal       India       Noida
 3       Sam      Lal       NULL        NULL
 4       Shyam    Singh     NULL        NULL
 5       Geeta    Rani      NULL        NULL
 6       Sheet    Devi      India       Delhi
 7       Leela    Bati      NULL        NULL

Oracle


SELECT e.ID as EMPID, e.FName, e.LNAME, a.Country, a.City
FROM Employee e
LEFT JOIN Address a
ON e.Id=a.EmployeeID;

Result : 

EMPID    FNAME    LNAME     Country     City
 1       Ram      Singh     India       Delhi
 2       Henry    Lal       India       Noida
 3       Sam      Lal       NULL        NULL
 4       Shyam    Singh     NULL        NULL
 5       Geeta    Rani      NULL        NULL
 6       Sheet    Devi      India       Delhi
 7       Leela    Bati      NULL        NULL


No comments:

Post a Comment