How to join two tables in SQL
In this article we are going to learn how to join two tables using INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN in SQL with examples which support both SQL Server and MySQL.
Refer below tables for the following examples.
Denormalized Data
Table: EmployeeData table
Id | Code | DepartmentId | DepartmentName |
1 | 0001 | 1 | Producing |
2 | 0002 | 2 | HR |
3 | 0003 | 1 | Producing |
4 | 0004 | NULL | NULL |
5 | 0005 | 3 | Marketing |
6 | 0006 | NULL | NULL |
Table: Employees
Id | Code | DepartmentId |
1 | 0001 | 1 |
2 | 0002 | 2 |
3 | 0003 | 1 |
4 | 0004 | NULL |
5 | 0005 | 3 |
6 | 0006 | NULL |
Sample data for employees
Table: Departments
Id | Name |
1 | Producing |
2 | HR |
3 | Marketing |
4 | IT |
5 | Accounting |
Sample data for departments
Use SQL Server or MySQL to run the following query to create sample tables for given examples.
CREATE TABLE Employees( Id int NOT NULL, Code varchar(10) NOT NULL, DepartmentId int NULL, PRIMARY KEY (Id) );
CREATE TABLE Departments( Id int NOT NULL, Name varchar(10) NOT NULL, PRIMARY KEY (Id) );
INSERT Employees VALUES (1, '0001', 1); INSERT Employees VALUES (2, '0002', 2); INSERT Employees VALUES (3, '0003', 1); INSERT Employees VALUES (4, '0004', NULL); INSERT Employees VALUES (5, '0005', 3); INSERT Employees VALUES (6, '0001', NULL);
INSERT Departments VALUES (1, 'Production'); INSERT Departments VALUES (2, 'HR'); INSERT Departments VALUES (3, 'Marketing'); INSERT Departments VALUES (4, 'IT'); INSERT Departments VALUES (5, 'Accounting'); |
Query to create tables with sample data
INNER JOIN
INNER JOIN selects all matching rows from both tables.
INNER JOIN
Example #1: INNER JOIN
The following SQL statement shows how to create an INNER JOIN using two tables.
SQL Statement / Example(s) |
SELECT Employees.Id, Employees.Code, Departments.Name AS Department FROM Employees INNER JOIN Departments ON Employees.DepartmentId = Departments.Id |
INNER JOIN in SQL
Id | Code | Department |
1 | 0001 | Production |
2 | 0002 | HR |
3 | 0003 | Production |
5 | 0005 | Marketing |
Query Result
LEFT JOIN
LEFT JOIN selects all matching rows from the left table.
LEFT JOIN
Example #2: LEFT JOIN
The following SQL statement shows how to create an LEFT JOIN using two tables.
SQL Statement / Example(s) |
SELECT Employees.Id, Employees.Code, Departments.Name AS Department FROM Employees LEFT JOIN Departments ON Employees.DepartmentId = Departments.Id |
LEFT JOIN in SQL
Id | Code | Department |
1 | 0001 | Production |
2 | 0002 | HR |
3 | 0003 | Production |
4 | 0004 | NULL |
5 | 0005 | Marketing |
6 | 0006 | NULL |
Query Result
RIGHT JOIN
RIGHT JOIN select all matching rows from the right table.
RIGHT JOIN
Example #3: RIGHT JOIN
The following SQL statement shows how to create an RIGHT JOIN using two tables.
SQL Statement / Example(s) |
SELECT Employees.Id, Employees.Code, Departments.Name AS Department, Departments.Id AS DepartmentId FROM Employees RIGHT JOIN Departments ON Employees.DepartmentId = Departments.Id; |
RIGHT JOIN in SQL
Id | Code | Department | DepartmentId |
1 | 0001 | Production | 1 |
3 | 0003 | Production | 1 |
2 | 0002 | HR | 2 |
5 | 0005 | Marketing | 3 |
NULL | NULL | IT | 4 |
NULL | NULL | Accounting | 5 |
Query Result
FULL JOIN
FULL JOIN select all rows from both tables.
FULL JOIN
Example #4: FULL JOIN
The following SQL statement shows how to create a FULL JOIN using two tables.
SQL Statement / Example(s) |
SELECT Employees.Id, Employees.Code, Departments.Name AS Department FROM Employees FULL JOIN Departments ON Employees.DepartmentId = Departments.Id; |
FULL JOIN in SQL Server
SQL Statement / Example(s) |
SELECT Employees.Id, Employees.Code, Departments.Name AS Department FROM Employees LEFT JOIN Departments ON Employees.DepartmentId = Departments.Id
UNION
SELECT Employees.Id, Employees.Code, Departments.Name AS Department FROM Employees RIGHT JOIN Departments ON Employees.DepartmentId = Departments.Id; |
FULL JOIN in MySQL
Id | Code | Department |
1 | 0001 | Production |
2 | 0002 | HR |
3 | 0003 | Production |
4 | 0004 | NULL |
5 | 0005 | Marketing |
6 | 0006 | NULL |
NULL | NULL | IT |
NULL | NULL | Accounting |
Query Result
Notes:
LEFT JOIN = LEFT OUTER JOIN
LEFT JOIN and LEFT OUTER JOIN results are same
RIGHT JOIN = RIGHT OUTER JOIN
RIGHT JOIN and RIGHT OUTER JOIN results are same
INNER JOIN = JOIN
INNER JOIN and JOIN results are same
