Sql Practical Examples With Joins Complete Guide

Prerequisites

1. Create Sample Databases and Tables: We'll create two simple tables named Employees and Departments. These tables will have some fictional data to demonstrate joins.

2. Employees Table

   • EmployeeID: Unique identifier for each employee.
   • FirstName: Employee's first name.
   • LastName: Employee's last name.
   • DepartmentID: ID of the department where the employee works.

3. Departments Table

   • DepartmentID: Unique identifier for each department.
   • DepartmentName: Name of the department.

Let's start by creating these tables and inserting some sample data into them.

-- Create the Employees table
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    DepartmentID INT,
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

-- Insert sample data into the Employees table
INSERT INTO Employees (EmployeeID, FirstName, LastName, DepartmentID) VALUES
(1, 'John', 'Doe', 1),
(2, 'Jane', 'Smith', 1),
(3, 'Emily', 'Jones', 2),
(4, 'Michael', 'Brown', 3),
(5, 'Sarah', 'Wilson', NULL);  -- No department assigned

-- Create the Departments table
CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName VARCHAR(50)
);

-- Insert sample data into the Departments table
INSERT INTO Departments (DepartmentID, DepartmentName) VALUES
(1, 'Sales'),
(2, 'Marketing'),
(3, 'Finance');

Now, let's proceed with different types of joins.

1. INNER JOIN

The most common type of join, INNER JOIN returns rows when there is a match in both tables.

Example: Retrieve the names and department names of employees who are assigned to a department.

SELECT 
    e.FirstName,
    e.LastName,
    d.DepartmentName
FROM 
    Employees e
INNER JOIN 
    Departments d ON e.DepartmentID = d.DepartmentID;

Steps Explained:

• FROM Employees e: We start with the Employees table. The alias e makes it easier to write and read queries involving multiple tables.
• INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID: Join condition is added to ensure that only those rows where EmployeeID in Employees matches DepartmentID in Departments are returned. The alias d is used for Departments table.
• SELECT: The columns to be retrieved are specified from each table.

Result:

FirstName | LastName | DepartmentName
----------|----------|-----------------
John      | Doe      | Sales          
Jane      | Smith    | Sales          
Emily     | Jones    | Marketing      
Michael   | Brown    | Finance        

Notice that the employee Sarah Wilson does not appear, as she has no matching DepartmentID.

2. LEFT JOIN (or LEFT OUTER JOIN)

Left join returns all records from the left table (Employees in this example) and the matched records from the right table (Departments). If there is no match, results are NULL on the side of the right table.

Example: Find the names and department names of all employees, including those without a department.

SELECT 
    e.FirstName,
    e.LastName,
    d.DepartmentName
FROM 
    Employees e
LEFT JOIN 
    Departments d ON e.DepartmentID = d.DepartmentID;
    -- or LEFT OUTER JOIN instead of LEFT JOIN

Steps Explained:

• LEFT JOIN Departments d ON e.DepartmentID = d.DepartmentID: This part ensures that all employees are included in the result set. Only matched rows from Departments will have actual values; otherwise, they will contain NULL.

Result:

FirstName  | LastName | DepartmentName
-----------|----------|-----------------
John       | Doe      | Sales           
Jane       | Smith    | Sales           
Emily      | Jones    | Marketing       
Michael    | Brown    | Finance         
Sarah      | Wilson   | NULL            

Here, Sarah Wilson appears with a NULL value in the DepartmentName column since her DepartmentID is NULL.

3. RIGHT JOIN (or RIGHT OUTER JOIN)

Right join returns all records from the right table (Departments in this case) and the matched records from the left table (Employees). It includes departments regardless of whether there’s an employee assigned to them or not.

Example: Fetch the names of departments along with the names of any corresponding employees. Departments without employees should still appear.

SELECT 
    e.FirstName,
    e.LastName,
    d.DepartmentName
FROM 
    Employees e
RIGHT JOIN 
    Departments d ON e.DepartmentID = d.DepartmentID;
    -- or RIGHT OUTER JOIN instead of RIGHT JOIN

Steps Explained:

• RIGHT JOIN Departments d ON e.DepartmentID = d.DepartmentID: By using the right join, the query includes all records from the Departments table. If no employees correspond to a department, the fields from the Employees table will be NULL.

Result:

FirstName  | LastName  | DepartmentName
----------|------------|----------------
John        | Doe        | Sales          
Jane        | Smith      | Sales          
Emily       | Jones      | Marketing      
Michael     | Brown      | Finance        
NULL        | NULL       | IT              -- Assuming 'IT' was added to the table

In this example, we’ve assumed an additional department 'IT' to show how departments without employees would look in the output (NULL for FirstName and LastName).

4. FULL JOIN (or FULL OUTER JOIN)

A full join returns all records when there is a match in either left (Employees) or right (Departments) table. Rows with no match will contain NULL on the side of the non-matching table.

Example: List all employees and departments, showing all combinations of employees and departments (including ones where there’s no match).

SELECT 
    e.FirstName,
    e.LastName,
    d.DepartmentName
FROM 
    Employees e
FULL JOIN 
    Departments d ON e.DepartmentID = d.DepartmentID;
    -- or FULL OUTER JOIN instead of FULL JOIN

Note: Not all SQL databases (like MySQL) support FULL JOIN directly. However, you can achieve similar functionality using a combination of UNION operator with LEFT AND RIGHT joins.

SELECT 
    e.FirstName,
    e.LastName,
    d.DepartmentName
FROM 
    Employees e
LEFT JOIN 
    Departments d ON e.DepartmentID = d.DepartmentID
UNION ALL
SELECT 
    e.FirstName,
    e.LastName,
    d.DepartmentName
FROM 
    Employees e
RIGHT JOIN 
    Departments d ON e.DepartmentID = d.DepartmentID
WHERE
    e.EmployeeID IS NULL;

Result:

FirstName  | LastName  | DepartmentName
-----------|------------|----------------
John        | Doe        | Sales          
Jane        | Smith      | Sales          
Emily       | Jones      | Marketing      
Michael     | Brown      | Finance        
Sarah       | Wilson     | NULL           
NULL        | NULL       | IT              -- Assuming 'IT' was added to the table

This result shows all employees and departments, even if there are no matching records between the tables.

5. CROSS JOIN

Cross join produces a Cartesian product of the two tables. This means every row from the first table is combined with every row from the second table, generating a huge result set.

Example: Combine every employee with every department to see possible assignments.

SELECT 
    e.FirstName,
    e.LastName,
    d.DepartmentName
FROM 
    Employees e
CROSS JOIN 
    Departments d;

Steps Explained:

• CROSS JOIN Departments d: Cross join combines every row from the Employees table with every row in the Departments table.

Result:

FirstName | LastName | DepartmentName
----------|----------|-----------------
John      | Doe      | Sales           
John      | Doe      | Marketing       
John      | Doe      | Finance         
Jane      | Smith    | Sales           
Jane      | Smith    | Marketing       
Jane      | Smith    | Finance           
Emily     | Jones    | Sales           
Emily     | Jones    | Marketing       
Emily     | Jones    | Finance           
Michael   | Brown    | Sales           
Michael   | Brown    | Marketing       
Michael   | Brown    | Finance           
Sarah     | Wilson   | Sales           
Sarah     | Wilson   | Marketing       
Sarah     | Wilson   | Finance           

Notice that each employee appears with each department, resulting in a significant number of rows. This join is typically useful for generating permutations or combinations of data but is less commonly used due to its size.

6. SELF JOIN

A self join is a join performed on a single table to create a result set based on relationships within the same table.

Example Scenario: Suppose we have a table called EmployeeHierarchy which represents the managerial structure within a company. Each row has an EmployeeID, FirstName, LastName, and a ManagerID that refers to another EmployeeID in the same table.

Creating the Self Join Table:

CREATE TABLE EmployeeHierarchy (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    ManagerID INT,
    FOREIGN KEY (ManagerID) REFERENCES EmployeeHierarchy(EmployeeID)
);

-- Insert sample data into the EmployeeHierarchy table
INSERT INTO EmployeeHierarchy (EmployeeID, FirstName, LastName, ManagerID) VALUES
(1, 'Alice', 'Johnson', NULL),  -- Alice is the CEO, no manager
(2, 'Bob', 'Williams', 1),       -- Bob reports to Alice
(3, 'Charlie', 'Davis', 1),     -- Charlie reports to Alice
(4, 'David', 'Miller', 2);       -- David reports to Bob

Self Join Example: Fetch the names of each employee along with their manager's name.

SELECT 
    e1.FirstName AS EmployeeFirstName,
    e1.LastName AS EmployeeLastName,
    CONCAT(e2.FirstName, ' ', e2.LastName) AS ManagerName
FROM 
    EmployeeHierarchy e1  -- Alias for employee
LEFT JOIN 
    EmployeeHierarchy e2  -- Alias for manager
ON 
    e1.ManagerID = e2.EmployeeID;

Steps Explained:

• LEFT JOIN EmployeeHierarchy e2: We join the same table under different aliases. e1 represents each employee, and e2 represents each manager.
• ON e1.ManagerID = e2.EmployeeID: The join is performed where the ManagerID of an employee corresponds to the EmployeeID of another row in the same table.

Result:

EmployeeFirstName | EmployeeLastName | ManagerName
------------------|-----------------|-------------
Alice             | Johnson         | NULL        
Bob               | Williams        | Alice Johnson
Charlie           | Davis           | Alice Johnson
David             | Miller          | Bob Williams 

• Alice Johnson is listed without a manager (since her ManagerID is NULL).
• Bob Williams and Charlie Davis report directly to Alice Johnson.
• David Miller reports to Bob Williams.

Conclusion

Understanding different SQL joins can greatly enhance your ability to analyze and manage relational data. Here’s a quick recap of what we covered:

1. INNER JOIN: Returns rows with matching values in both tables.
2. LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table and matched rows from the right table; unmatched rows from the right table become NULL.
3. RIGHT JOIN (or RIGHT OUTER JOIN): Returns all rows from the right table and matched rows from the left table; unmatched rows from the left table become NULL.
4. FULL JOIN (or FULL OUTER JOIN): Includes all rows when there is a match in either table; unmatched rows become NULL.
5. CROSS JOIN: Combines each row from the left table with every row from the right table (Cartesian product).
6. SELF JOIN: Performs a join operation on a single table.