Skip to content

4.joins.md

Latest commit

 

History

History
225 lines (203 loc) · 5.47 KB

4.joins.md

File metadata and controls

225 lines (203 loc) · 5.47 KB

SQL JOINs

JOINs are fundamental operations in SQL that allow you to combine rows from two or more tables based on related columns. Think of JOINs as a more structured version of MongoDB's $lookup - they enable you to establish relationships between tables and perform complex queries.

Types of JOINs

1. INNER JOIN

  • Purpose: Returns only the matching rows from both tables.
  • Visual Representation:
Table A         Table B
   ╔═══╗         ╔═══╗
   ║   ║         ║   ║
   ║ ▒▒▒▒▒▒▒▒▒▒▒▒▒ ║
   ║   ║         ║   ║
   ╚═══╝         ╚═══╝
   (Matched area in gray)
  • Syntax:
SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;
  • Example:
-- Get employee details with their department names
SELECT 
    e.EmployeeID,
    e.FirstName,
    e.LastName,
    d.DepartmentName
FROM Employees e
INNER JOIN Departments d
ON e.DepartmentID = d.DepartmentID;

2. LEFT (OUTER) JOIN

  • Purpose: Returns all rows from the left table and matching rows from the right table.
  • Visual Representation:
Table A         Table B
   ╔═══╗         ╔═══╗
   ║███║         ║   ║
   ║███▒▒▒▒▒▒▒▒▒▒▒ ║
   ║███║         ║   ║
   ╚═══╝         ╚═══╝
   (Table A fully included)
  • Syntax:
SELECT columns
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;
  • Example:
-- Get all employees and their department names (if any)
SELECT 
    e.EmployeeID,
    e.FirstName,
    e.LastName,
    d.DepartmentName
FROM Employees e
LEFT JOIN Departments d
ON e.DepartmentID = d.DepartmentID;

3. RIGHT (OUTER) JOIN

  • Purpose: Returns all rows from the right table and matching rows from the left table.
  • Visual Representation:
Table A         Table B
   ╔═══╗         ╔═══╗
   ║   ║         ║███║
   ║ ▒▒▒▒▒▒▒▒▒▒▒███║
   ║   ║         ║███║
   ╚═══╝         ╚═══╝
   (Table B fully included)
  • Syntax:
SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column;
  • Example:
-- Get all departments and their employees (if any)
SELECT 
    d.DepartmentName,
    e.FirstName,
    e.LastName
FROM Employees e
RIGHT JOIN Departments d
ON e.DepartmentID = d.DepartmentID;

4. FULL (OUTER) JOIN

  • Purpose: Returns all rows from both tables, with matching records where available.
  • Visual Representation:
Table A         Table B
   ╔═══╗         ╔═══╗
   ║███║         ║███║
   ║███▒▒▒▒▒▒▒▒▒███║
   ║███║         ║███║
   ╚═══╝         ╚═══╝
   (Both tables fully included)
  • Syntax:
SELECT columns
FROM table1
FULL OUTER JOIN table2
ON table1.column = table2.column;
  • Example:
-- Get all employees and all departments, showing all possible combinations
SELECT 
    e.EmployeeID,
    e.FirstName,
    e.LastName,
    d.DepartmentName
FROM Employees e
FULL OUTER JOIN Departments d
ON e.DepartmentID = d.DepartmentID;

Practical Session 🔴

-- Create sample tables
CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName VARCHAR(50),
    Location VARCHAR(50)
);

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    DepartmentID INT,
    Salary DECIMAL(10,2)
);

-- Insert sample data
INSERT INTO Departments VALUES
(1, 'IT', 'New York'),
(2, 'HR', 'London'),
(3, 'Sales', 'Paris'),
(4, 'Marketing', 'Tokyo');

INSERT INTO Employees VALUES
(1, 'John', 'Doe', 1, 75000),
(2, 'Jane', 'Smith', 2, 65000),
(3, 'Bob', 'Johnson', 1, 80000),
(4, 'Alice', 'Williams', 3, 70000),
(5, 'Charlie', 'Brown', NULL, 60000);

-- Different JOIN examples

-- 1. Find all employees with their department information
SELECT 
    e.FirstName,
    e.LastName,
    d.DepartmentName,
    d.Location
FROM Employees e
INNER JOIN Departments d
ON e.DepartmentID = d.DepartmentID;

-- 2. Find all employees (including those without departments)
SELECT 
    e.FirstName,
    e.LastName,
    COALESCE(d.DepartmentName, 'No Department') as Department
FROM Employees e
LEFT JOIN Departments d
ON e.DepartmentID = d.DepartmentID;

-- 3. Find all departments (including those without employees)
SELECT 
    d.DepartmentName,
    COUNT(e.EmployeeID) as EmployeeCount
FROM Departments d
LEFT JOIN Employees e
ON d.DepartmentID = e.DepartmentID
GROUP BY d.DepartmentName;

-- 4. Complex JOIN with aggregation
SELECT 
    d.DepartmentName,
    COUNT(e.EmployeeID) as EmployeeCount,
    ROUND(AVG(e.Salary), 2) as AvgSalary,
    MIN(e.Salary) as MinSalary,
    MAX(e.Salary) as MaxSalary
FROM Departments d
LEFT JOIN Employees e
ON d.DepartmentID = e.DepartmentID
GROUP BY d.DepartmentName
ORDER BY EmployeeCount DESC;

JOINs are essential for:

  • Combining related data from multiple tables
  • Creating comprehensive reports
  • Analyzing relationships between different entities
  • Maintaining data normalization while querying denormalized views

Unlike MongoDB's $lookup, SQL JOINs:

  • Are more performant for complex relationships
  • Provide stronger consistency guarantees
  • Support multiple types of joins (INNER, LEFT, RIGHT, FULL)
  • Are more declarative and easier to understand
  • Are fundamental to the relational model