Today we will learn about joining multiple tables in postgresql.

Why?

We join tables in databases because most real-world data is spread across multiple tables to avoid redundancy and improve organization. A single table usually doesn’t contain all the information you need for a query. Joining tables allows you to combine related data in meaningful ways.

-- see all the tables in the database
select
	table_name
from
	information_schema.tables
where
	table_schema = 'public';

We currently have only users table. Imagine each user having several travels. We need to store those travel objects in another table and connect them with a user.

Creating travels table

We will create another table to store travels.

create table travels (
	id SERIAL primary key,
	user_id INTEGER not null references users(id),
	destination_city VARCHAR(100) not null,
	destination_country VARCHAR(100) not null,
	travel_date DATE not null,
	return_date DATE,
	travel_type VARCHAR(50) default 'vacation',
	budget DECIMAL(10, 2),
	notes TEXT,
	status VARCHAR(20) default 'planned',
	created_at TIMESTAMP default CURRENT_TIMESTAMP,
	updated_at TIMESTAMP default CURRENT_TIMESTAMP
);

Note that we have a column called user_id which is an INTEGER and not null and references user's id. While connecting tables, we use references keyword and target_table(target_column).

Insert travels

insert into travels
	(user_id, destination_city, destination_country, travel_date, return_date, travel_type, budget, status)
values
	(2, 'Tokyo', 'Japan', '2025-09-15', '2025-09-22', 'vacation', 2000, 'planned');

Note that we are providing user_id while inserting a travel so that we are connecting the travel object with a user with the id of 2.

You can create mock data using mockaroo and import by following methods that we mentioned in day-2.

Querying with a join

Select users with their travel destinations.

select
	u.first_name,
	string_agg(t.destination_city, ', ') as destinations
from
	users as u
join travels as t
    on
	t.user_id = u.id
where
	u.first_name is not null
group by
	u.first_name;

Join types

Inner join is the most common join type and join is the shorthand for inner join.

• INNER = only matching pairs.
• LEFT/RIGHT = keep everything from one side, fill with NULLs if no match.
• FULL = keep everything from both.
• CROSS = everything with everything.

End of Day 4

Today we learned about joining tables.

Tomorrow we will learn about schemas and why they do exist.