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 |
+----+-------+--------+
Table: Address
+----+------------+---------+--------+
| 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