SQL Tutorial 7 Joins : Inner : Left : Right
SQL Join Inner
In SQL, an inner join is used to combine rows from two or more tables based on a related column between them. It selects records that have matching values in both tables. Here is the basic syntax for an inner join:
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;
Let’s assume we have two tables, “Customers” and “Orders”, and we want to retrieve data where there are matching customer IDs in both tables. Here is an example:
— Customers Table
CREATE TABLE Customers (
CustomerID int,
CustomerName varchar(255)
);
— Orders Table
CREATE TABLE Orders (
OrderID int,
CustomerID int,
OrderDate date
);
— Sample data for Customers
INSERT INTO Customers (CustomerID, CustomerName)
VALUES (1, ‘John Doe’),
(2, ‘Jane Smith’),
(3, ‘Michael Johnson’);
— Sample data for Orders
INSERT INTO Orders (OrderID, CustomerID, OrderDate)
VALUES (101, 1, ‘2023-10-01’),
(102, 3, ‘2023-10-03’),
(103, 2, ‘2023-10-05’);
— Inner Join example
SELECT Customers.CustomerName, Orders.OrderID, Orders.OrderDate
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;
This query will retrieve the CustomerName, OrderID, and OrderDate for all orders placed by customers, displaying only the records that have matching CustomerID values in both tables.
SQL Inner Join with Example
Sure, let’s consider an example of two tables, “Employees” and “Departments,” where we want to retrieve data for employees along with their corresponding department information based on a common key, which is the “DepartmentID” in both tables.
Here are the sample tables and data:
— Employees Table
CREATE TABLE Employees (
EmployeeID INT,
EmployeeName VARCHAR(255),
DepartmentID INT
);
— Departments Table
CREATE TABLE Departments (
DepartmentID INT,
DepartmentName VARCHAR(255)
);
— Inserting sample data into the Employees table
INSERT INTO Employees (EmployeeID, EmployeeName, DepartmentID)
VALUES
(1, ‘John Doe’, 1),
(2, ‘Jane Smith’, 2),
(3, ‘Michael Johnson’, 1),
(4, ‘Emma Brown’, 3);
— Inserting sample data into the Departments table
INSERT INTO Departments (DepartmentID, DepartmentName)
VALUES
(1, ‘HR’),
(2, ‘Finance’),
(3, ‘IT’);
Now, let’s perform an inner join on these tables to get the details of employees along with their corresponding departments:
— Inner Join Example
SELECT Employees.EmployeeName, Departments.DepartmentName
FROM Employees
INNER JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;
The result of this query will be:
| EmployeeName | DepartmentName |
|——————-|—————-|
| John Doe | HR |
| Jane Smith | Finance |
| Michael Johnson | HR |
| Emma Brown | IT |
This query combines the data from the two tables based on the common DepartmentID column, displaying the names of employees along with their respective department names.
SQL Left Join with Example
Certainly, a left join in SQL is used to return all records from the left table (the first table specified) and the matched records from the right table (the second table specified).
If there are no matches, it still returns the records from the left table along with NULL values for the columns from the right table.
Here’s an example with two tables, “Employees” and “Departments,” where we want to retrieve data for all employees, including those who are not assigned to any department. We will use the same tables as in the previous example.
— Left Join Example
SELECT Employees.EmployeeName, Departments.DepartmentName
FROM Employees
LEFT JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;
The result of this query will be:
| EmployeeName | DepartmentName |
|——————-|—————-|
| John Doe | HR |
| Jane Smith | Finance |
| Michael Johnson | HR |
| Emma Brown | IT |
In this case, since all employees are assigned to a department, the result of the left join is the same as that of the inner join in the previous example.
However, if there were employees without a department, the left join would have included those employees in the result with NULL values for the DepartmentName column.
SQL Right Join with example
Certainly, a right join in SQL is used to return all records from the right table (the second table specified) and the matched records from the left table (the first table specified).
If there are no matches, it still returns the records from the right table along with NULL values for the columns from the left table.
Here’s an example using the same tables, “Employees” and “Departments,” where we want to retrieve data for all departments, including those that have no employees assigned.
— Right Join Example
SELECT Employees.EmployeeName, Departments.DepartmentName
FROM Employees
RIGHT JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;
The result of this query will be:
| EmployeeName | DepartmentName |
|——————-|—————-|
| John Doe | HR |
| Jane Smith | Finance |
| Michael Johnson | HR |
| Emma Brown | IT |
SQL Self Join with example
A self join in SQL is used when you want to combine rows with other rows in the same table. This is often used when a table has a foreign key that references its own primary key. Let’s consider an example using an “Employees” table, where we want to retrieve data for employees along with the names of their managers.
Here’s the sample table and data:
— Employees Table
CREATE TABLE Employees (
EmployeeID INT,
EmployeeName VARCHAR(255),
ManagerID INT
);
— Inserting sample data into the Employees table
INSERT INTO Employees (EmployeeID, EmployeeName, ManagerID)
VALUES
(1, ‘John Doe’, 3),
(2, ‘Jane Smith’, 3),
(3, ‘Michael Johnson’, NULL),
(4, ‘Emma Brown’, 2);
Now, let’s perform a self join on the “Employees” table to get the details of employees along with the names of their managers:
— Self Join Example
SELECT e.EmployeeName AS Employee, m.EmployeeName AS Manager
FROM Employees e
LEFT JOIN Employees m
ON e.ManagerID = m.EmployeeID;
The result of this query will be:
| Employee | Manager |
|——————|——————|
| John Doe | Michael Johnson |
| Jane Smith | Michael Johnson |
| Michael Johnson | NULL |
| Emma Brown | Jane Smith |
This query combines the data from the “Employees” table by matching the “ManagerID” column with the “EmployeeID” column to retrieve the names of employees along with the names of their respective managers.
SQL FULL JOIN with Example
In SQL, a full join returns all records when there is a match in either the left or right table. If there are no matches, it still returns the records from both tables, with NULL values for the columns from the table that does not have a matching row.
Here’s an example using two tables, “Employees” and “Departments,” where we want to retrieve data for all employees and all departments, including those that do not have corresponding matches in the other table.
Consider the following tables and data:
— Employees Table
CREATE TABLE Employees (
EmployeeID INT,
EmployeeName VARCHAR(255),
DepartmentID INT
);
— Departments Table
CREATE TABLE Departments (
DepartmentID INT,
DepartmentName VARCHAR(255)
);
— Inserting sample data into the Employees table
INSERT INTO Employees (EmployeeID, EmployeeName, DepartmentID)
VALUES
(1, ‘John Doe’, 1),
(2, ‘Jane Smith’, 2),
(3, ‘Michael Johnson’, 1),
(4, ‘Emma Brown’, NULL);
— Inserting sample data into the Departments table
INSERT INTO Departments (DepartmentID, DepartmentName)
VALUES
(1, ‘HR’),
(2, ‘Finance’),
(3, ‘IT’);
Now, let’s perform a full join on these tables to get the details of all employees and all departments:
— Full Join Example
SELECT Employees.EmployeeName, Departments.DepartmentName
FROM Employees
FULL JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;
The result of this query will be:
| EmployeeName | DepartmentName |
|——————-|—————-|
| John Doe | HR |
| Jane Smith | Finance |
| Michael Johnson | HR |
| Emma Brown | NULL |
| NULL | IT |
In this example, the full join returns all the records from both tables, including the employee without a department and the department without any employees.
How to work SQL Left Join and Right Join work with Example
SQL left join and right join are used to combine rows from two or more tables based on a related column between them, prioritizing either all records from the left table or all records from the right table, respectively. Here are some explanations along with examples for both:
Left Join:
A left join returns all records from the left table (the first table specified) and the matched records from the right table (the second table specified). If there are no matches, it still returns the records from the left table along with NULL values for the columns from the right table.
Example using two tables, “Employees” and “Departments”:
— Left Join Example
SELECT Employees.EmployeeName, Departments.DepartmentName
FROM Employees
LEFT JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;
Right Join:
A right join returns all records from the right table (the second table specified) and the matched records from the left table (the first table specified). If there are no matches, it still returns the records from the right table along with NULL values for the columns from the left table.
Example using the same tables, “Employees” and “Departments”:
— Right Join Example
SELECT Employees.EmployeeName, Departments.DepartmentName
FROM Employees
RIGHT JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;
Both these joins are crucial for combining data from different tables, especially when some records might not have a direct match in the other table. They help us analyze data across tables and identify relationships between them.
— Employees Table
CREATE TABLE Employees (
EmployeeID INT,
EmployeeName VARCHAR(255),
DepartmentID INT
);
— Departments Table
CREATE TABLE Departments (
DepartmentID INT,
DepartmentName VARCHAR(255)
);
— Inserting sample data into the Employees table
INSERT INTO Employees (EmployeeID, EmployeeName, DepartmentID)
VALUES
(1, ‘John Doe’, 1),
(2, ‘Jane Smith’, 2),
(3, ‘Michael Johnson’, 1),
(4, ‘Emma Brown’, NULL);
— Inserting sample data into the Departments table
INSERT INTO Departments (DepartmentID, DepartmentName)
VALUES
(1, ‘HR’),
(2, ‘Finance’),
(3, ‘IT’);
Now, let’s perform a full join on these tables to get the details of all employees and all departments:
— Full Join Example
SELECT Employees.EmployeeName, Departments.DepartmentName
FROM Employees
FULL JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;
The result of this query will be:
| EmployeeName | DepartmentName |
|——————-|—————-|
| John Doe | HR |
| Jane Smith | Finance |
| Michael Johnson | HR |
| Emma Brown | NULL |
| NULL | IT |
In this example, the full join returns all the records from both tables, including the employee without a department and the department without any employees.