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.StateResults
| 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.