In-Depth Tutorial: Multitable Queries (Joins) with Codes With Pankaj

1. Introduction to Joins

Joins combine data from multiple tables using a common column. They are essential for querying relational databases.

Example Tables:

-- Parent Table: Departments
CREATE TABLE Departments (
    DeptID INT PRIMARY KEY,
    DeptName VARCHAR(50)
);

-- Child Table: Employees
CREATE TABLE Employees (
    EmpID INT PRIMARY KEY,
    EmpName VARCHAR(50),
    DeptID INT,
    FOREIGN KEY (DeptID) REFERENCES Departments(DeptID)
);

---

2. Two-Table Query Example (Equi-Join)

Goal: List employees with their department names.

Equi-Join uses = to match columns.

Step 1: Use WHERE Clause (Old Syntax)

SELECT Employees.EmpName, Departments.DeptName
FROM Employees, Departments
WHERE Employees.DeptID = Departments.DeptID;

Step 2: Use INNER JOIN (ANSI SQL)

SELECT E.EmpName, D.DeptName
FROM Employees E
INNER JOIN Departments D ON E.DeptID = D.DeptID;

---

3. Parent/Child Queries

Parent-child relationships use foreign keys.

Example:

-- Find departments with no employees (Parent without Children)
SELECT D.DeptName
FROM Departments D
LEFT JOIN Employees E ON D.DeptID = E.DeptID
WHERE E.EmpID IS NULL;

---

4. Alternative Join Syntax

Use JOIN with USING for same column names:

SELECT EmpName, DeptName
FROM Employees
JOIN Departments USING (DeptID);

---

5. Joins with Row Selection

Filter results after joining:

SELECT E.EmpName, D.DeptName
FROM Employees E
INNER JOIN Departments D ON E.DeptID = D.DeptID
WHERE D.DeptName = 'Sales';

---

6. Multiple Matching Columns

Join on multiple columns:

-- Example: Orders and Products (ProductID + SupplierID)
SELECT *
FROM Orders O
JOIN Products P ON O.ProductID = P.ProductID AND O.SupplierID = P.SupplierID;

---

7. Natural Joins

Automatically joins on same-named columns:

SELECT EmpName, DeptName
FROM Employees
NATURAL JOIN Departments; -- Works if both have DeptID

---

8. Three or More Tables

Add a third table (Projects):

SELECT E.EmpName, D.DeptName, P.ProjectName
FROM Employees E
JOIN Departments D ON E.DeptID = D.DeptID
JOIN Projects P ON E.EmpID = P.EmpID;

---

9. Non-Equi-Joins

Use operators like >, <, or BETWEEN:

-- Find employees with salaries in a specific grade range
SELECT E.EmpName, S.Grade
FROM Employees E
JOIN SalaryGrades S ON E.Salary BETWEEN S.MinSalary AND S.MaxSalary;

---

10. SQL Considerations

• Qualified Names: Employees.DeptID avoids ambiguity.
• Self-Joins: Use aliases to join a table to itself.

  SELECT A.EmpName AS Employee, B.EmpName AS Manager
  FROM Employees A
  JOIN Employees B ON A.ManagerID = B.EmpID;

• Performance: Index foreign keys and avoid SELECT *.

---

11. Outer Joins

Left Outer Join

Returns all rows from the left table:

SELECT E.EmpName, D.DeptName
FROM Employees E
LEFT JOIN Departments D ON E.DeptID = D.DeptID;

Right Outer Join

Returns all rows from the right table.

Full Outer Join

Combines left and right joins (not supported in MySQL).

---

12. SQL Standard Joins

• CROSS JOIN: Cartesian product.

  SELECT * FROM Employees CROSS JOIN Departments;

• INNER JOIN: Standard equi-join.
• OUTER JOIN: Includes unmatched rows.

---

13. Common Mistakes

• Forgetting the join condition (creates a Cartesian product).
• Using NATURAL JOIN with mismatched column names.

---

14. Summary

• Equi-Joins use = to match columns.
• Outer Joins include unmatched rows.
• Self-Joins require table aliases.

Practice all examples at www.codeswithpankaj.com!

---

Author: Pankaj Chouhan

Website: Codes With Pankaj