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

Vendor	Name	State
1	ABC Supplies	TX
2	XYZ Traders	CA
3	Fast Movers	FL
4	Prime Goods	NULL

HQ States Table

State	Description
TX	Texas
CA	California
FL	Florida
NY	New 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 Name	State Name
ABC Supplies	Texas
XYZ Traders	California
Fast Movers	Florida

• 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 Name	State Name
ABC Supplies	Texas
XYZ Traders	California
Fast Movers	Florida
Prime Goods	NULL

• 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 Name	State Name
ABC Supplies	Texas
XYZ Traders	California
Fast Movers	Florida
NULL	New 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 Name	State Name
ABC Supplies	Texas
XYZ Traders	California
Fast Movers	Florida
Prime Goods	NULL
NULL	New 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 Type	Includes 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.