A JOIN clause is used to combine rows from two or more tables, based on a related column between them.
(INNER) JOIN: Returns records that have matching values in both tables
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:
Mysql
// Query: inner join query without where
Query 1:->
SELECT e.ID as EMPID, e.FName, e.LNAME, a.Country, a.City
FROM Employee e
INNER JOIN Address a
ON e.Id=a.EmployeeID;
// Query: inner join query with where
Query 2:->
SELECT e.ID as EMPID, e.FName, e.LNAME, a.Country, a.City
FROM Employee e, Address a
WHERE e.Id=a.EmployeeID;
Result :
EMPID FNAME LNAME Country City
1 Ram Singh India Delhi
2 Henry Lal India Noida
6 Sheet Devi India Delhi
Oracle
// Query: inner join query without where
Query 1:->
SELECT e.ID as EMPID, e.FName, e.LNAME, a.Country, a.City
FROM Employee e
INNER JOIN Address a
ON e.Id=a.EmployeeID;
// Query: inner join query with where
Query 2:->
SELECT e.ID as EMPID, e.FName, e.LNAME, a.Country, a.City
FROM Employee e, Address a
WHERE e.Id=a.EmployeeID;
Result :
EMPID FNAME LNAME Country City
1 Ram Singh India Delhi
2 Henry Lal India Noida
6 Sheet Devi India Delhi
No comments:
Post a Comment