SQL Joins

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.