SQL INNER JOIN

The INNER JOIN clause in SQL combines rows from two or more tables based on a related column between them. It's a fundamental technique for retrieving data from multiple tables efficiently.

INNER JOIN: Definition and Usage

INNER JOIN returns only the rows where there is a match in both tables being joined. If a row in one table doesn't have a corresponding match in the other table based on the join condition, that row is excluded from the results. This is extremely useful when you need to link related data from multiple tables.

Example Tables: Products and Categories

Let's look at sample data from the 'Products' and 'Categories' tables:

Products Table

Categories Table

These tables are linked via the CategoryID column.

INNER JOIN Examples

Basic INNER JOIN

This query joins 'Products' and 'Categories' based on matching CategoryID values.

SELECT ProductID, ProductName, CategoryName
FROM Products
INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID;
      

ProductID | ProductName    | CategoryName
---------------------------------------
1         | Chais           | Beverages
2         | Chang            | Beverages
3         | Aniseed Syrup   | Condiments

      

INNER JOIN with Explicit Column Names

It's best practice to specify table names to avoid ambiguity, especially when column names are the same in multiple tables.

SELECT Products.ProductID, Products.ProductName, Categories.CategoryName
FROM Products
INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID;
      

ProductID | ProductName    | CategoryName
---------------------------------------
1         | Chais           | Beverages
2         | Chang            | Beverages
3         | Aniseed Syrup   | Condiments
      

JOIN vs. INNER JOIN

JOIN and INNER JOIN are functionally equivalent; INNER JOIN is just more explicit.

SELECT Products.ProductID, Products.ProductName, Categories.CategoryName
FROM Products
JOIN Categories ON Products.CategoryID = Categories.CategoryID;
      

ProductID | ProductName    | CategoryName
---------------------------------------
1         | Chais           | Beverages
2         | Chang            | Beverages
3         | Aniseed Syrup   | Condiments
      

Joining Three Tables

This example joins three tables: 'Orders', 'Customers', and 'Shippers'. (Assumes the existence of these tables with appropriate linking columns.)

SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID
INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID;
      

OrderID | CustomerName                     | ShipperName
--------------------------------------------------------
(OrderID) | (CustomerName)                   | (ShipperName)