14. Joining Tables: INNER JOIN

Core Idea: INNER JOIN combines records from two or more tables based on a related column between them. Rows appear in the result set only if the join condition evaluates to true in both tables.

Syntax Shape

            SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
            FROM Orders
            INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;
        

Multiple Table Joins

            SELECT o.OrderID, c.CustomerName, p.ProductName, od.Quantity
            FROM Orders AS o
            INNER JOIN Customers AS c ON o.CustomerID = c.CustomerID
            INNER JOIN OrderDetails AS od ON o.OrderID = od.OrderID
            INNER JOIN Products AS p ON od.ProductID = p.ProductID;
        

Practical Applications

Linking sales transactions to customer accounts, retrieving category titles for inventory items, and consolidating normalized database relationships.

Advanced Deep Dive ADVANCED

Join Types Comparison: INNER JOIN excludes non-matching rows from both sides. To preserve unmatched rows, consider LEFT JOIN, RIGHT JOIN, or FULL OUTER JOIN.

Join Conditions & Indexing: Ensure foreign key and primary key columns used in the ON clause have appropriate indexes (typically non-clustered indexes on foreign keys) to allow index seek execution plans.

NULL Values in Joins: In standard SQL, NULL = NULL evaluates to UNKNOWN. Rows with NULL keys will never match in an INNER JOIN.

Physical Join Operators: SQL Server optimizes joins into Nested Loops, Merge Join, or Hash Match operators based on table size and indexing.