Joins in SQL

joins.sql

CREATE TABLE author(author_id integer PRIMARY KEY, name text, age integer);
INSERT INTO author VALUES(1, "Alice", 25);
INSERT INTO author VALUES(2, "Bob", 32);
INSERT INTO author VALUES(3, "Charlie", 29);

CREATE TABLE posts(id integer PRIMARY KEY, author_id integer, post_name text);
INSERT INTO posts VALUES(1, 1, "foo");
INSERT INTO posts VALUES(2, 1, "bar");
INSERT INTO posts VALUES(3, 1, "baz");
INSERT INTO posts VALUES(4, 2, "quz");
INSERT INTO posts VALUES(5, 2, "xyz");

-- CROSS JOIN:
-- Is like a cross-product, it's every combination between the two tables
SELECT * FROM author, posts;
-- Alternate syntax:
SELECT * FROM author CROSS JOIN posts;

-- Creating an index allows the SQL engine to efficiently search for the index (i.e. it builds a tree instead of traversing a list sequentially)
CREATE INDEX author_index
ON posts (author_id);

-- INNER JOIN:
-- Intersect the ids where they're equal
-- For every author, give me the posts that they wrote
SELECT * FROM author, posts WHERE author.author_id = posts.author_id;
-- Alternate syntax:
SELECT * FROM author INNER JOIN posts ON author.author_id = posts.author_id;

-- NATURAL JOIN
-- A type of inner join where the id fields are the same name
-- Notice that 'Charlie' doesn't appear in the table.
SELECT * FROM author NATURAL JOIN posts;

-- OUTER JOIN:
-- Same as a natural join except also return NULL data
SELECT * FROM author LEFT OUTER JOIN posts ON author.author_id = posts.author_id;