Skip to content

SQL Joins

FULL-STACK WEB DEVELOPMENT BOOTCAMP

2024 COHORT SIGNUPS END TOMORROW

How to perform a simple join between two database tables

Joins are a very powerful tool. Remember relational algebra from the database intro module?

Joins are applied relational algebra.

Suppose you have 2 tables, people and cars:

CREATE TABLE people (
  age INT NOT NULL,
  name CHAR(20) NOT NULL PRIMARY KEY
);

CREATE TABLE cars (
  brand CHAR(20) NOT NULL,
  model CHAR(20) NOT NULL,
  owner CHAR(20) NOT NULL PRIMARY KEY
);

We add some data:

INSERT INTO people VALUES (37, 'Flavio');
INSERT INTO people VALUES (8, 'Roger');
INSERT INTO cars VALUES ('Ford', 'Fiesta', 'Flavio');
INSERT INTO cars VALUES ('Ford', 'Mustang', 'Roger');

Now say that we want to correlate the two tables, because the police stopped Roger driving, looks young, and want to know his age from their database.

Roger is my dog, but let’s suppose dogs can drive cars.

We can create a join with this syntax:

SELECT age FROM people JOIN cars ON people.name = cars.owner WHERE cars.model='Mustang';

We’ll get this result back:

 age 
-----
   8

What is happening? We are joining the two tables cars on two specific columns: name from the people table, and owner from the cars table.

Joins are a topic that can grow in complexity because there are many different kind of joins that you can use to do fancier things with multiple tables, but here is the most basic example.

β†’ Read my SQL Tutorial
  • Learn modern web development in my BOOTCAMP (SIGNUP END TOMORROW FEB 20, 2024)
  • THE VALLEY OF CODE (+ PRO), your web development manual
  • I wrote 15+ coding BOOKS, all available in THE VALLEY OF CODE PRO
  • Indie solopreneur internet business masterclass SOLO LAB (summer 2024)