$ settings --editor

$ settings --general

$ save --settings

SQL for beginners

Combining tables with INNER JOIN #

In the last lesson, we joined customers and orders by writing out the full table name in front of each column, like customers.first_name. That works, but it gets verbose once your queries involve several tables. SQL lets us give each table a short alias to keep things readable.

Table aliases #

You assign an alias by writing it right after the table name (no AS required, though you can use it if you like):

SELECT
    oi.quantity,
    p.name
FROM
    order_items oi
INNER JOIN
    products p
ON
    oi.product_id = p.id;
  • order_items oi: from now on in this query, order_items can be referred to as oi.
  • products p: likewise, products can be referred to as p.
  • ON oi.product_id = p.id: the join condition, written using the aliases.

Aliases don't change the result at all, they just make the query shorter and easier to read, which matters a lot once you start joining three or four tables together.

Why the ON clause matters #

Every INNER JOIN needs an ON clause that says how the two tables relate. In our database, order_items.product_id and products.id both refer to the same product, so that's what we join on:

SELECT
    oi.quantity,
    p.name,
    p.category
FROM
    order_items oi
INNER JOIN
    products p
ON
    oi.product_id = p.id;

Result:

+----------+-------------------+-------------+
| quantity |        name       |  category   |
+----------+-------------------+-------------+
|    3     |   Wireless Mouse  | Electronics |
|    1     | Bluetooth Speaker | Electronics |
|   .....  |       .....       |    .....    |

If you joined on the wrong columns (say, oi.id = p.id), the database wouldn't complain, it would just silently match up the wrong rows. Always double check that your ON condition reflects the real relationship between the tables.

Exercise #

Write a query that joins order_items (aliased oi) and products (aliased p) to return the product name and the quantity for every order item.

  • The result has a name column and a quantity column.
  • The result contains one row per order item, pairing the correct product name with the correct quantity.
Solution 
SELECT
    p.name,
    oi.quantity
FROM
    order_items oi
INNER JOIN
    products p
ON
    oi.product_id = p.id;
Output will be displayed here