Skip to main content
Share / Export

Understanding SQL Joins

Getting Started

When joining tables you must choose a Join Type, which determines how records from each table are combined and what data appears in the final result.

Imagine you have two tables.

  • AVPM - AP Vendors
    • Contains a list of vendors and the state they are located in.
  • HQST - HQ States
    • Contains a list of states.

AP Vendors Table

VendorNameState
1ABC SuppliesTX
2XYZ TradersCA
3Fast MoversFL
4Prime GoodsNULL

HQ States Table

StateDescription
TXTexas
CACalifornia
FLFlorida
NYNew York

    Inner Join

    An INNER JOIN returns only the rows where there is a match in both tables.

    SELECT *
FROM APVM Vendors
INNER JOIN HQST States ON Vendors.State=States.State

    Results

    Vendor NameState Name
    ABC SuppliesTexas
    XYZ TradersCalifornia
    Fast MoversFlorida
    • Prime Goods is missing because it doesn’t have a StateID (NULL).
    • New York is missing because no vendor is located there.

    Left Outer Join

    A LEFT OUTER JOIN returns all records from the left table (Vendors), plus matching records from the right table (States). If there’s no match, it fills with NULL.

    SELECT * 
    FROM APVM Vendors 
    LEFT OUTER JOIN HQST States ON Vendors.State=States.State

    Results

    Vendor NameState Name
    ABC SuppliesTexas
    XYZ TradersCalifornia
    Fast MoversFlorida
    Prime GoodsNULL
    • Prime Goods is included, but its state is NULL.
    • New York is still missing because no vendor is located there.

    Right Outer Join

    A RIGHT JOIN returns all records from the right table (States), plus matching records from the left table (Vendors). If there’s no match, it fills with NULL.

    SELECT * 
    FROM APVM Vendors 
    RIGHT OUTER JOIN HQST States ON Vendors.State=States.State

    Results

    Vendor NameState Name
    ABC SuppliesTexas
    XYZ TradersCalifornia
    Fast MoversFlorida
    NULLNew York
    • New York appears, but with NULL because no vendor is located there.
    • Prime Goods is missing because RIGHT JOIN focuses on all states, and Prime Goods has no state assigned.

    Full Outer Join

    A FULL JOIN returns all records from both tables, with NULLs where there is no match.

    SELECT * 
    FROM APVM Vendors 
    FULL OUTER JOIN HQST States ON Vendors.State=States.State

    Results

    Vendor NameState Name
    ABC SuppliesTexas
    XYZ TradersCalifornia
    Fast MoversFlorida
    Prime GoodsNULL
    NULLNew York
    • Prime Goods is included with NULL because it has no state assigned.
    • New York is included with NULL because it has no vendors.

    Summary Table

    Join TypeIncludes Vendors with No State?Includes States with No Vendors?
    INNER JOIN❌ No❌ No
    LEFT OUTER JOIN✅ Yes❌ No
    RIGHT OUTER JOIN❌ No✅ Yes
    FULL OUTER JOIN✅ Yes✅ Yes

    When to Use Each Join

    • INNER JOIN → When you only need records that match in both tables.
    • LEFT JOIN → When you need all vendors, even if they don’t have a state.
    • RIGHT JOIN → When you need all states, even if they don’t have vendors.
    • FULL JOIN → When you need all vendors and states, even if there is no match.