SQL: Joins

When you have a lot of tables and data, it’s common to feel the need to “join” the tables based on some common information.

For example you have a products table and a orders table.

The orders table has the id of a product ordered in product_id (imagine an order can only be for 1 single product).

The products table has the product name in its name column.

Something like this:

  buyer TEXT,
  product_id INTEGER

CREATE TABLE products (
  name TEXT

INSERT INTO orders (buyer, product_id) VALUES ('[email protected]', 1);
INSERT INTO orders (buyer, product_id) VALUES ('[email protected]', 2);

INSERT INTO products (name) VALUES ('milk');
INSERT INTO products (name) VALUES ('bread');

Now suppose you want to get the names of the products ordered by user with email [email protected], here’s a query you would use:

SELECT name FROM products INNER JOIN orders ON orders.product_id = products.id

Maybe you want the emails of people that bought milk:

SELECT orders.buyer FROM products INNER JOIN orders ON orders.product_id = products.id WHERE products.name = 'milk'

This is the simplest kind of join, called “inner join”.

Joins can be much more complex, but this is a start.

Lessons in this unit:

0: Introduction
1: Using TablePlus
2: Create a Postgres database
3: Create a new table
4: Add data to a table
5: Query data with SELECT
6: Delete data from the database
7: Update data with UPDATE
8: Null constraints
9: Unique and primary keys
10: Updating a table structure
11: ▶︎ Joins
12: DEMO Using Vercel Postgres COMING SOON
Are you intimidated by Git? Can’t figure out merge vs rebase? Are you afraid of screwing up something any time you have to do something in Git? Do you rely on ChatGPT or random people’s answer on StackOverflow to fix your problems? Your coworkers are tired of explaining Git to you all the time? Git is something we all need to use, but few of us really master it. I created this course to improve your Git (and GitHub) knowledge at a radical level. Launching May 21, 2024. Join the waiting list!