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:
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
buyer TEXT,
product_id INTEGER
);
CREATE TABLE products (
id SERIAL PRIMARY KEY,
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.