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 >,

Feb 10, 2025 - 02:41

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