http://www.sql-join.com/sql-join-types/
In below: The relationship between the two tables is specified by the customer_id key, which is the “primary key” in customers table and a “foreign key” in the orders table:
| customer_id | first_name | last_name | address | city | state | zipcode | |
|---|---|---|---|---|---|---|---|
| 1 | George | Washington | gwashington@usa.gov | 3200 Mt Vernon Hwy | Mount Vernon | VA | 22121 |
| 2 | John | Adams | jadams@usa.gov | 1250 Hancock St | Quincy | MA | 02169 |
| 3 | Thomas | Jefferson | tjefferson@usa.gov | 931 Thomas Jefferson Pkwy | Charlottesville | VA | 22902 |
| 4 | James | Madison | jmadison@usa.gov | 11350 Constitution Hwy | Orange | VA | 22960 |
| 5 | James | Monroe | jmonroe@usa.gov | 2050 James Monroe Parkway | Charlottesville | VA | 22902 |
| order_id | order_date | amount | customer_id |
|---|---|---|---|
| 1 | 07/04/1776 | $234.56 | 1 |
| 2 | 03/14/1760 | $78.50 | 3 |
| 3 | 05/23/1784 | $124.00 | 2 |
| 4 | 09/03/1790 | $65.50 | 3 |
Note that (1) not every customer in our customers table has placed an order and (2) there are a few orders for which no customer record exists in our customers table.
Inner Join Query:
select first_name, last_name, order_date, amount from customer inner join orders on customer.customer_id=orders.customer_id
Result:
| first_name | last_name | order_date | amount | |
|---|---|---|---|---|
| george | washington | 07/04/1776 | $234.56 | |
| john | adams | 05/23/1784 | $124.00 | |
| Thomas | Jefferson | 03/14/1760 | $78.50 | |
| Thomas | Jefferson | 09/03/1790 | $65.50 |
