Hi all,
The SQL joins are used for combine rows from two or more table based on a common field between them.We are discuss this topic today. It fetch data from two or more tables and which is joined to appear as single set of data. Join Keyword is used in SQL queries for joining two or more tables. Minimum required condition for joining table, is (n-1) where n, is number of tables.
Different types of JOINs are available in SQL .They are:
- INNER JOIN
- LEFT JOIN
- RIGHT JOIN
- FULL JOIN
1) INNER Join or EQUI Join
The INNER JOIN selects all rows from both tablesresults based on the match between the columns in both tables. Returns all the rows when there is atleast one matches in both tables.
SYNTAX
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;
or
SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name=table2.column_name;
Example of INNER JOIN. The Employee1 table
| Employee_ID | Employee_Name |
|---|---|
| 1 | Jose |
| 2 | Thomas |
| 3 | Joseph |
The Employee2 table
| Employee_ID | Salary |
|---|---|
| 1 | 4000 |
| 2 | 8000 |
Inner JOIN query will be,
SELECT * from Employee1, Employee2 where Employee1.Employee_ID = Employee2.Employee_ID;
The result will be
| Employee_ID | Employee_Name | Employee_ID | Salary |
|---|---|---|---|
| 1 | Jose | 1 | 4000 |
| 2 | Thomas | 2 | 8000 |
2) LEFT Join
Return all rows from the left table, and the matched rows from the right table.
SYNTAX
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;
or
SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;
Example of LEFT JOIN. The Employee1 table
| Employee_ID | Employee_Name |
|---|---|
| 1 | Jose |
| 2 | Thomas |
| 3 | Joseph |
The Employee2 table
| Employee_ID | Salary |
|---|---|
| 1 | 4000 |
| 2 | 8000 |
LEFT JOIN query will be,
SELECT Employee_ID, Employee_Name,Salary
FROM Employee1
LEFT JOIN Employee2
ON Employee1.Employee_ID = Employee2.Employee_ID;
The result will be
| Employee_ID | Employee_Name | Salary |
|---|---|---|
| 1 | Jose | 4000 |
| 2 | Thomas | 8000 |
| 3 | Joseph | NULL |
The LEFT JOIN keyword returns all rows from the left table (Employee1), with the matching rows in the right table (Employee2).
3) RIGHT Join
The right join return all rows from the right table, and the matched rows from the left table.
SYNTAX
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;
or
SELECT column_name(s)
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name=table2.column_name;
Example of RIGHT JOIN. The Employee1 table
| Employee_ID | Employee_Name |
|---|---|
| 1 | Jose |
| 2 | Thomas |
| 3 | Joseph |
The Employee2 table
| Employee_ID | Salary |
|---|---|
| 1 | 4000 |
| 2 | 8000 |
RIGHT JOIN query will be,
SELECT Employee_ID, Employee_Name,Salary
FROM Employee1
RIGHT JOIN Employee2
ON Employee1.Employee_ID = Employee2.Employee_ID;
The result will be
| Employee_ID | Employee_Name | Salary |
|---|---|---|
| 1 | Jose | 4000 |
| 2 | Thomas | 8000 |
The RIGHT JOIN keyword returns all rows from the right table (Employee2), with the matching rows in the left table (Employee1).
4) FULL OUTER Join
Return all the rows from the left table and right table
SYNTAX
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;
Example of FULL OUTER JOIN. The Employee1 table
| Employee_ID | Employee_Name |
|---|---|
| 1 | Jose |
| 2 | Thomas |
| 3 | Joseph |
The Employee2 table
| Employee_ID | Salary |
|---|---|
| 1 | 4000 |
| 2 | 8000 |
RIGHT JOIN query will be,
SELECT Employee_Name,Salary
FROM Employee1
FULL OUTER JOIN Employee2
ON Employee1.Employee_ID = Employee2.Employee_ID;
The result will be
| Employee_Name | Salary |
|---|---|
| Jose | 4000 |
| Thomas | 8000 |
| Joseph |
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.