Learn INNER, LEFT, RIGHT, FULL, CROSS Joins & Self-Joins - Textnotes

Learn INNER, LEFT, RIGHT, FULL, CROSS Joins & Self-Joins

Master SQL joins and table relationships in this beginner-friendly guide. Learn INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, CROSS JOIN, self-joins, and foreign key relationships. Practice joining Employees and Departments, listing employees without departments, and fetching students with enrolled courses.

1. Introduction

Joins allow you to combine data from multiple tables based on a related column, usually a foreign key.

They are essential for relational databases to extract meaningful information from multiple tables.

2. Types of Joins

2.1 INNER JOIN

  1. Returns only matching rows from both tables.

Syntax:


SELECT a.column1, b.column2
FROM TableA a
INNER JOIN TableB b
ON a.CommonColumn = b.CommonColumn;

Example:


SELECT Employees.Name, Departments.DeptName
FROM Employees
INNER JOIN Departments
ON Employees.Department = Departments.DeptName;

2.2 LEFT JOIN (LEFT OUTER JOIN)

  1. Returns all rows from the left table, and matching rows from the right table.
  2. If no match, right table columns are NULL.

Example:


-- List all employees including those without a department
SELECT Employees.Name, Departments.DeptName
FROM Employees
LEFT JOIN Departments
ON Employees.Department = Departments.DeptName;

2.3 RIGHT JOIN (RIGHT OUTER JOIN)

  1. Returns all rows from the right table, and matching rows from the left table.

Example:


-- List all departments including those without employees
SELECT Employees.Name, Departments.DeptName
FROM Employees
RIGHT JOIN Departments
ON Employees.Department = Departments.DeptName;

2.4 FULL OUTER JOIN

  1. Returns all rows from both tables, matching where possible, and NULL where there is no match.

Example:


SELECT Employees.Name, Departments.DeptName
FROM Employees
FULL OUTER JOIN Departments
ON Employees.Department = Departments.DeptName;

2.5 CROSS JOIN

  1. Returns all possible combinations of rows from both tables.

Example:


SELECT Employees.Name, Departments.DeptName
FROM Employees
CROSS JOIN Departments;

2.6 Self-Join

  1. Joins a table to itself to compare rows within the same table.

Example:


-- Find pairs of employees in the same department
SELECT e1.Name AS Employee1, e2.Name AS Employee2, e1.Department
FROM Employees e1
INNER JOIN Employees e2
ON e1.Department = e2.Department
WHERE e1.ID <> e2.ID;

3. Foreign Key Relationships

  1. A foreign key links a column in one table to a primary key in another.
  2. Ensures referential integrity between tables.

Example:


CREATE TABLE Departments (
DeptID INT PRIMARY KEY,
DeptName VARCHAR(50)
);

CREATE TABLE Employees (
ID INT PRIMARY KEY,
Name VARCHAR(50),
DepartmentID INT,
FOREIGN KEY (DepartmentID) REFERENCES Departments(DeptID)
);

4. Practical Exercises

  1. Join Employees and Departments to display employee names with department names.
  2. List all employees without departments using LEFT JOIN.
  3. Fetch students with their enrolled courses using INNER JOIN.
  4. Use CROSS JOIN to list all possible employee-department combinations.
  5. Use a self-join to find employees in the same department.

5. Tips for Beginners

  1. Always identify common columns to join tables.
  2. Choose the join type based on whether you want all rows or only matching rows.
  3. Use aliases (e1, e2) for clarity, especially with self-joins.
  4. Foreign keys enforce data consistency and simplify joins.


Next Step: After mastering joins and relationships, the next module is Subqueries, where you’ll learn to write queries within queries, including correlated and non-correlated subqueries.