SQL Joins
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:
Employee table
The second table's name is Joining.
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:
result of innerjoin
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:
Employee table
The second table's name is Joining.
joining 1
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:
result of LEFT OUTER JOIN
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:
Employee table
The second table's name is Joining.
joining table
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:
Employee table
The second table's name is Joining.
joining 1
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.
result of FULL OUTER JOIN
