This content originally appeared on DEV Community and was authored by 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.DeptIDavoids 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 JOINwith 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
This content originally appeared on DEV Community and was authored by Codes With Pankaj
Codes With Pankaj | Sciencx (2025-02-10T01:01:16+00:00) In-Depth Tutorial: Multitable Queries (Joins) with Codes With Pankaj. Retrieved from https://www.scien.cx/2025/02/10/in-depth-tutorial-multitable-queries-joins-with-codes-with-pankaj/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.