Join Types
- • INNER JOIN: only matching rows
- • LEFT JOIN: all left rows, matching right rows
- • RIGHT JOIN and FULL JOIN: less common
Example: Users and Posts
SELECT u.name, p.title
FROM users u
JOIN posts p ON p.user_id = u.id
WHERE p.published = true
ORDER BY p.created_at DESC;Many-to-Many with Junction Tables
CREATE TABLE tags (
id SERIAL PRIMARY KEY,
name TEXT UNIQUE NOT NULL
);
CREATE TABLE post_tags (
post_id INT REFERENCES posts(id),
tag_id INT REFERENCES tags(id),
PRIMARY KEY (post_id, tag_id)
);