To illustrate SQL JOINs, consider two example tables: Employees and Departments.

First, let's define the table schemas and populate them with sample data.

Employees Table DDL: CREATE TABLE Employees ( EmployeeID INTEGER PRIMARY KEY, Name VARCHAR(50), DepartmentID INTEGER );

Departments Table DDL: CREATE TABLE Departments ( DepartmentID INTEGER PRIMARY KEY, DepartmentName VARCHAR(50) );

Sample Data Insertion: INSERT INTO Employees (EmployeeID, Name, DepartmentID) VALUES (1, 'Alice', 101), (2, 'Bob', 102), (3, 'Charlie', NULL), (4, 'David', 101), (5, 'Eve', NULL), (6, 'Frank', 999); -- Department 999 does not exist in Departments table

INSERT INTO Departments (DepartmentID, DepartmentName) VALUES (101, 'Sales'), (102, 'Marketing'), (103, 'HR'), -- No matching employee (104, 'Engineering'); -- No matching employee

Current state of the tables:

Employees Table:

Departments Table:

INNER JOIN

Explanation: The INNER JOIN keyword returns rows when there is at least one match in both tables based on the join condition. It combines rows from two tables only if the join predicate (the ON clause) evaluates to true for both tables. Rows that do not have a match in both tables are excluded from the result set.

Business Question: Which employees are currently assigned to a department, and what are those departments?

SQL Query: SELECT E.EmployeeID, E.Name, D.DepartmentName FROM Employees E INNER JOIN Departments D ON E.DepartmentID = D.DepartmentID;

Expected Output (Brief): This query would return Alice (Sales), Bob (Marketing), and David (Sales). Charlie and Eve (who have NULL DepartmentID), and Frank (whose DepartmentID 999 doesn't exist) would be excluded. Similarly, HR and Engineering departments (which have no employees) would not appear.

LEFT JOIN (or LEFT OUTER JOIN)

Explanation: The LEFT JOIN (also known as LEFT OUTER JOIN) keyword returns all rows from the left table (the first table mentioned in the FROM clause) and the matching rows from the right table. If there is no match for a row in the left table, the columns from the right table will have NULL values in the result set. This join type is useful when you want to retrieve all records from one table, regardless of whether they have a corresponding entry in another table.

Business Question: List all employees and their corresponding department, if any. Include employees who are not yet assigned to a department.

SQL Query: SELECT E.EmployeeID, E.Name, D.DepartmentName FROM Employees E LEFT JOIN Departments D ON E.DepartmentID = D.DepartmentID;

Expected Output (Brief): This query would return all employees: Alice (Sales), Bob (Marketing), Charlie (NULL), David (Sales), Eve (NULL), and Frank (NULL). For Charlie, Eve, and Frank, the DepartmentName column would show NULL because they either have a NULL DepartmentID or their DepartmentID does not exist in the Departments table. Departments like HR and Engineering would not be shown as they are not on the "left" side and have no matching employees.

FULL OUTER JOIN

Explanation: The FULL OUTER JOIN (or OUTER JOIN) keyword returns all rows when there is a match in one of the tables. It effectively combines the results of both LEFT JOIN and RIGHT JOIN. It returns all rows from both the left and right tables, with NULL values for the columns of the table that does not have a match. This join type is useful for seeing all data from both tables, showing where matches exist and where they do not. Note that FULL OUTER JOIN syntax might vary or not be supported in all SQL databases (e.g., MySQL prior to 8.0 doesn't directly support it, requiring a UNION of LEFT JOIN and RIGHT JOIN). For this example, we assume a SQL standard compliant database.

Business Question: Show all employees and all departments. For employees without a department, list them with NULL for department name. For departments without any employees, list them with NULL for employee details.

SQL Query: SELECT E.EmployeeID, E.Name, D.DepartmentName FROM Employees E FULL OUTER JOIN Departments D ON E.DepartmentID = D.DepartmentID;

Expected Output (Brief): This query would return:

• Alice (Sales)
• Bob (Marketing)
• Charlie (NULL)
• David (Sales)
• Eve (NULL)
• Frank (NULL)
• NULL (HR) - indicating HR has no employees
• NULL (Engineering) - indicating Engineering has no employees

It combines all employees (including those without departments or with non-existent department IDs) and all departments (including those without employees), filling NULLs where there's no match.