What is the difference between the join operator and the left join operator?

The JOIN operator (or INNER JOIN) returns rows where there are matches in both tables. The LEFT JOIN (or LEFT OUTER JOIN) returns all rows from the left table and matching rows from the right table. If there are no matches in the right table, NULL is returned.

-- INNER JOIN
SELECT *
FROM table1
JOIN table2 ON table1.id = table2.id;

-- LEFT JOIN
SELECT *
FROM table1
LEFT JOIN table2 ON table1.id = table2.id;

Example:

Suppose we have two tables: Users and Orders.

Users table:

id	name
1	Ivan
2	Petr
3	Anna

Orders table:

id	user_id	product
10	1	Book
11	1	Movie
12	2	Music

• INNER JOIN of Users and Orders on user_id will return:

user_id	name	order_id	product
1	Ivan	10	Book
1	Ivan	11	Movie
2	Petr	12	Music

• LEFT JOIN of Users (left) and Orders (right) on user_id will return:

user_id	name	order_id	product
1	Ivan	10	Book
1	Ivan	11	Movie
2	Petr	12	Music
3	Anna	NULL	NULL