4 major types of Joins are used while working on multiple tables in SQL databases:
INNER JOIN: It is also known as SIMPLE JOIN which returns all rows from BOTH tables when it has at least one matching column.
Syntax:
|
SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON column_name1=column_name2; |
For Example,
In this example, we have a table Employee with the following data:
The second table's name is Joining.
Enter the following SQL statement:
|
SELECT Employee.Emp_id, Joining.Joining_Date
FROM Employee
INNER JOIN Joining
ON Employee.Emp_id = Joining.Emp_id
ORDER BY Employee.Emp_id; |
There will be 4 records selected. Results are:
Employee and Orders tables have a matching customer_id value.
LEFT JOIN (LEFT OUTER JOIN): This join returns all rows from the LEFT table and its matched rows from a RIGHT table.
Syntax:
|
SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON column_name1=column_name2; |
For Example,
In this example, we have a table Employee with the following data:
The second table's name is Joining.
Enter the following SQL statement:
|
SELECT Employee.Emp_id, Joining.Joining_Date
FROM Employee
LEFT OUTER JOIN Joining
ON Employee.Emp_id = Joining.Emp_id
ORDER BY Employee.Emp_id; |
There will be 4 records selected. You will see the following results:
RIGHT JOIN (RIGHT OUTER JOIN): This joins returns all rows from the RIGHT table and its matched rows from the LEFT table.
Syntax:
|
SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON column_name1=column_name2; |
For Example,
In this example, we have a table Employee with the following data:
The second table's name is Joining.
Enter the following SQL statement:
|
SELECT Employee.Emp_id, Joining.Joining_Date FROM Employee
RIGHT JOIN Joining
ON Employee.Emp_id = Joining.Emp_id
ORDER BY Employee.Emp_id; |
Output:
| Emp_id |
Joining_Date |
| E0012 |
2016/04/18 |
| E0013 |
2016/04/19 |
| E0014 |
2016/05/01 |
FULL JOIN (FULL OUTER JOIN): This joins returns all results when there is a match either in the RIGHT table or in the LEFT table.
Syntax:
|
SELECT column_name(s)
FROM table_name1
FULL OUTER JOIN table_name2
ON column_name1=column_name2; |
For Example,
In this example, we have a table Employee with the following data:
The second table's name is Joining.
Enter the following SQL statement:
|
SELECT Employee.Emp_id, Joining.Joining_Date
FROM Employee
FULL OUTER JOIN Joining
ON Employee.Emp_id = Joining.Emp_id
ORDER BY Employee.Emp_id; |
There will be 8 records selected. These are the results that you should see.
