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 has the
id of a product ordered in
product_id (imagine an order can only be for 1 single product).
products table has the product name in its
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 ('firstname.lastname@example.org', 1); INSERT INTO orders (buyer, product_id) VALUES ('email@example.com', 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
firstname.lastname@example.org, 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 this unit:
|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|
|9:||Unique and primary keys|
|10:||Updating a table structure|
|12:||DEMO Using Vercel Postgres COMING SOON|