Inner Join SQL Tutorial (7)

SQL Tutorial 7 Joins

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.

Leave a Reply