4.4 JOINs (INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN)

4.4.1 INNER JOIN

An INNER JOIN returns only the rows where there is a match in both tables.

-- Create sample tables
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    EmployeeName VARCHAR(50),
    DepartmentID INT
);
CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName VARCHAR(50)
);
-- Insert sample data
INSERT INTO Employees VALUES (1, 'John Doe', 1);
INSERT INTO Employees VALUES (2, 'Jane Smith', 2);
INSERT INTO Departments VALUES (1, 'IT');
INSERT INTO Departments VALUES (2, 'HR');
-- INNER JOIN query
SELECT Employees.EmployeeID, EmployeeName, DepartmentName
FROM Employees
INNER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

EmployeeID	EmployeeName	DepartmentName
1	John Doe	IT
2	Jane Smith	HR

4.4.2 LEFT JOIN

A LEFT JOIN returns all rows from the left table and the matched rows from the right table. If there's no match, NULL values are returned for columns from the right table.

-- LEFT JOIN query
SELECT Employees.EmployeeID, EmployeeName, DepartmentName
FROM Employees
LEFT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

EmployeeID	EmployeeName	DepartmentName
1	John Doe	IT
2	Jane Smith	HR
3	Sam Johnson	NULL

4.4.3 RIGHT JOIN

A RIGHT JOIN returns all rows from the right table and the matched rows from the left table. If there's no match, NULL values are returned for columns from the left table.

-- RIGHT JOIN query
SELECT Employees.EmployeeID, EmployeeName, DepartmentName
FROM Employees
RIGHT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

EmployeeID	EmployeeName	DepartmentName
1	John Doe	IT
2	Jane Smith	HR
NULL	NULL	Marketing

4.4.4 FULL JOIN

A FULL JOIN returns all rows when there is a match in either the left or right table. If there's no match, NULL values are returned for columns from the table without a match.

-- FULL JOIN query
SELECT Employees.EmployeeID, EmployeeName, DepartmentName
FROM Employees
FULL JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

EmployeeID	EmployeeName	DepartmentName
1	John Doe	IT
2	Jane Smith	HR
3	Sam Johnson	NULL
NULL	NULL	Marketing

Feel free to execute these SQL queries in your SQL Server environment to see the results. The table format helps to present the output in a clear and organized manner.