SQL
A SQL join clause combines records from two or more tables in a database.
Types of Joins in C:
Inner Join:
This type of join returns those records which have matching values in both tables.
Syntax
SELECT column_name(s) FROM table1
INNER JOIN table2ON table1.column_name = table2.column_name
INNER JOIN table2ON table1.column_name = table2.column_name
Inner Joins
| Emp ID | Emp Name | Age | Address |
|---|---|---|---|
| 1 | Himani | 22 | Delhi |
| 2 | Hemanth | 23 | Mumbai |
| 3 | Hema | 24 | Kolkata |
Project Table:
| Project ID | Emp ID | Project Name |
|---|---|---|
| 111 | 1 | project1 |
| 222 | 2 | project12 |
| 333 | 4 | project3 |
| 444 | 5 | project4 |
| 555 | 1 | project5 |
SELECT Employee.EmpID, Employee.Empname, Project.ProjectID,Project.ProjectName FROM Employee (
INNER JOIN Project ON Employee.EmpID= Project.EmpID;
INNER JOIN Project ON Employee.EmpID= Project.EmpID;
Result:
| Emp ID | Emp Name | Project ID | Project Name |
|---|---|---|---|
| 1 | Himani | 111 | project1 |
| 2 | Hemanth | 222 | project2 |
| 1 | Himani | 555 | project5 |
SELECT Employee.EmpID, Employee.Empname, Project.ProjectID,Project.ProjectName FROM Employee
INNER JOIN Project ON Employee.EmpID= Project.EmpID;
INNER JOIN Project ON Employee.EmpID= Project.EmpID;
Left Join:
The LEFT JOIN or the LEFT OUTER JOIN returns all the records from the left table and also those records which satisfy a condition from the right table.
Syntax
SELECT column_name(s) FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name;
LEFT JOIN table2 ON table1.column_name = table2.column_name;
SELECT Employee.Empname, Project.ProjectID,Project.ProjectName FROM Employee
RIGHT JOIN Project ON Employee.EmpID = Project.EmpID
RIGHT JOIN Project ON Employee.EmpID = Project.EmpID
Result:
| Empname | Project ID | Projetname |
|---|---|---|
| Himani | 111 | project1 |
| Hemanth | 222 | project2 |
| Himani | 555 | project3 |
| Hema | NULL | NULL |
Right Join:
The RIGHT JOIN or the RIGHT OUTER JOIN returns all the records from the right table and also those records which satisfy a condition from the left table.
Syntax
SELECT column_name(s) FROM table1
RIGHT JOIN table2 ON table1.column_name = table2.column_name;
RIGHT JOIN table2 ON table1.column_name = table2.column_name;
Right Joins
Result:
| Empname | Project ID | Projetname |
|---|---|---|
| Himani | 111 | project1 |
| Himani | 555 | project5 |
| Hemanth | 222 | project2 |
| NULL | 333 | project3 |
| NULL | 444 | project4 |
Full Join:
Full join or the FULL OUTER Join returns all those records which either have a match in the left(Table1) or the right(Table2) table.
Syntax
SELECT column_name(s) FROM table1
FULL OUTER JOIN table2 ON table1.column_name = table2.column_name
WHERE condition;
FULL OUTER JOIN table2 ON table1.column_name = table2.column_name
WHERE condition;
Full Joins
Example
SELECT Employee.Empname, Project.ProjectID,Project.ProjectName FROM Employee
LEFT JOIN Project ON Employee.EmpID = Project.EmpID
UNION SELECT Employee.Empname, Project.ProjectID,Project.ProjectName FROM Employee
RIGHT JOIN Project ON Employee.EmpID = Project.EmpID
LEFT JOIN Project ON Employee.EmpID = Project.EmpID
UNION SELECT Employee.Empname, Project.ProjectID,Project.ProjectName FROM Employee
RIGHT JOIN Project ON Employee.EmpID = Project.EmpID
Result:
| Empname | Project ID | Projetname |
|---|---|---|
| Himani | 111 | project1 |
| Hemanth | 222 | project2 |
| Himani | 555 | project5 |
| Hema | NULL | NULL |
| NULL | 333 | project3 |
| NULL | 444 | project4 |
