What is the difference between JOIN and INNER JOIN?

JOIN vs. INNER JOIN: Understanding SQL's Core Data Retrieval

Explore the fundamental differences and similarities between JOIN and INNER JOIN in SQL, and learn how to effectively use them to combine data from multiple tables.

In SQL, combining data from multiple tables is a common and essential task. The JOIN clause is used for this purpose, allowing you to retrieve related data based on common columns between tables. Among the various types of joins, INNER JOIN is arguably the most frequently used. A common point of confusion for beginners is whether JOIN and INNER JOIN are different, or if one is a subset of the other. This article will clarify this distinction, demonstrate their usage, and provide practical examples.

The Relationship Between JOIN and INNER JOIN

The simplest answer is that INNER JOIN is the default type of JOIN. When you use the JOIN keyword without specifying a type (e.g., LEFT, RIGHT, FULL), SQL databases interpret it as an INNER JOIN. This means that JOIN and INNER JOIN are functionally identical in most SQL implementations. They both return only the rows that have matching values in both tables being joined.

flowchart TD
    A[Start Query] --> B{JOIN Keyword Used?}
    B -->|Yes| C{Type Specified?}
    C -->|No (Default)| D[INNER JOIN]
    C -->|Yes (e.g., LEFT, RIGHT)| E[Specific JOIN Type]
    D --> F[Return only matching rows from both tables]
    E --> G[Return rows based on specified JOIN type]
    F --> H[End Query]
    G --> H[End Query]

Decision flow for SQL JOIN interpretation

How INNER JOIN Works

An INNER JOIN creates a new result table by combining column values of two tables (or more) based upon a join-predicate. The query compares each row of the first table with each row of the second table to find all pairs of rows that satisfy the join-predicate. When the join-predicate is satisfied, column values for each matched pair of rows are combined into a result row. Rows that do not have a match in the other table are excluded from the result set.

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

Example of an explicit INNER JOIN

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

Example of an implicit INNER JOIN (using just JOIN)

Visualizing INNER JOIN

Imagine two sets of data, Table A and Table B. An INNER JOIN acts like an intersection, returning only the data points that exist in both sets. Any data unique to Table A or Table B is excluded from the result.

INNER JOIN as a Venn Diagram

Other JOIN Types (Brief Overview)

To fully appreciate INNER JOIN, it's helpful to briefly understand how it differs from other join types:

• LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table, and the matching rows from the right table. If there is no match for the right table, NULL is returned.
• RIGHT JOIN (or RIGHT OUTER JOIN): Returns all rows from the right table, and the matching rows from the left table. If there is no match for the left table, NULL is returned.
• FULL JOIN (or FULL OUTER JOIN): Returns all rows when there is a match in one of the tables. If there is no match, NULL is returned for the columns from the table that has no match.

erDiagram
    CUSTOMER ||--o{ ORDER : places
    ORDER ||--o{ PRODUCT : contains
    CUSTOMER { 
        int CustomerID PK
        varchar CustomerName
        varchar ContactName
    }
    ORDER { 
        int OrderID PK
        int CustomerID FK
        date OrderDate
    }
    PRODUCT { 
        int ProductID PK
        varchar ProductName
        decimal Price
    }

Example Entity-Relationship Diagram for Customers, Orders, and Products

Understanding these distinctions is crucial for selecting the correct join type to achieve your desired result set. For most scenarios where you only want to see data that exists in all joined tables, INNER JOIN (or simply JOIN) is the appropriate choice.