$ settings --editor

$ settings --general

$ save --settings

SQL for beginners

What is a table join #

So far, every query we've written has looked at a single table. In a real database, data is usually spread across several tables. For example, in our sample database:

  • The customers table holds each customer's first_name, last_name, age, and gender.
  • The orders table holds each order's id, customer_id, order_date, and status.

Notice that the orders table doesn't store the customer's name, only a customer_id. If we want to see the customer's name next to their order status, we need to combine, or join, rows from both tables.

A join combines rows from two tables based on a related column between them. Here, customers.id and orders.customer_id refer to the same customer, so we can use them to match up the rows.

INNER JOIN syntax #

The most common type of join is INNER JOIN:

SELECT
    columns
FROM
    table_1
INNER JOIN
    table_2
ON
    table_1.column = table_2.column;
  • INNER JOIN table_2: the table we want to combine with table_1.
  • ON table_1.column = table_2.column: tells the database how the two tables relate to each other. Without an ON condition, the database wouldn't know which row in table_1 belongs with which row in table_2.

Example #

SELECT
    customers.first_name,
    orders.status
FROM
    customers
INNER JOIN
    orders
ON
    customers.id = orders.customer_id;

Result:

+------------+-----------+
| first_name |  status   |
+------------+-----------+
|  Jonathan  |  Shipped  |
|   Tammy    | Delivered |
|    .....   |   .....   |

Each row in the result pairs a customer's first_name with the status of one of their orders. If a customer placed 3 orders, they will show up 3 times in the result, once per order. Notice we wrote customers.first_name and orders.status instead of just first_name and status — this is called qualifying a column name with its table. It's required whenever the column name could be ambiguous (for example, both tables might have an id column), and it's good practice any time you're joining tables, even if the column names don't clash.

Exercise #

Write a query that joins customers and orders on customers.id = orders.customer_id, returning the customer's first_name and the order's status for every order.

  • The result has a first_name column and a status column.
  • The result contains one row for every order, pairing the correct first_name with the correct status.
Solution 
SELECT
    customers.first_name,
    orders.status
FROM
    customers
INNER JOIN
    orders
ON
    customers.id = orders.customer_id;
Output will be displayed here