Before diving into SQL joins, let’s create two simple tables: employees and departments. These will serve as the base for our join examples.
CREATE TABLE employees (
EmployeeID INT PRIMARY KEY,
EmployeeName VARCHAR(100),
DepartmentID INT
);
2. Create the departments table:
CREATE TABLE departments (
DepartmentID INT PRIMARY KEY,
DepartmentName VARCHAR(100)
);
Now, let’s insert sample data into these tables for our JOIN operations:
INSERT INTO employees (EmployeeID, EmployeeName, DepartmentID)
VALUES (1, 'John Doe', 1),
(2, 'Jane Smith', 2),
(3, 'Robert Brown', 3),
(4, 'Emma White', NULL);
2. Insert data into the departments table:
INSERT INTO departments (DepartmentID, DepartmentName)
VALUES (1, 'IT'),
(2, 'HR'),
(3, 'Finance');
The INNER JOIN returns only the rows where there is a match in both tables. If there is no match, no rows are returned.
employees and departments tables:| EmployeeID | Employee Name | Department |
|---|---|---|
| 1 | John Doe | IT |
| 2 | Jane Smith | HR |
| 3 | Robert Brown | Finance |
With an INNER JOIN, we can retrieve employee details along with their department information:
SELECT employees.EmployeeID, employees.EmployeeName, departments.DepartmentName
FROM employees
INNER JOIN departments
ON employees.DepartmentID = departments.DepartmentID;
The LEFT JOIN returns all rows from the left table and matching rows from the right table. If no match is found, NULL values are returned for the right table columns.
SELECT employees.EmployeeID, employees.EmployeeName, departments.DepartmentName
FROM employees
LEFT JOIN departments
ON employees.DepartmentID = departments.DepartmentID;
The RIGHT JOIN returns all rows from the right table and matching rows from the left table. If no match is found, NULL values are returned for the left table columns.
SELECT employees.EmployeeID, employees.EmployeeName, departments.DepartmentName
FROM employees
RIGHT JOIN departments
ON employees.DepartmentID = departments.DepartmentID;
The FULL JOIN (also called FULL OUTER JOIN) returns all rows when there is a match in either the left or the right table. If there is no match, the missing side will contain NULL values.
SELECT employees.EmployeeID, employees.EmployeeName, departments.DepartmentName
FROM employees
FULL JOIN departments
ON employees.DepartmentID = departments.DepartmentID;
