SQL inner join

 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   |
+----+-------+--------+
TableAddress
+----+------------+---------+--------+
| 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