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.
