What JOIN is used by default when you just write JOIN? What types of JOINs exist?

By default, when simply writing JOIN in SQL, INNER JOIN is used. It returns only those rows that have matches in both tables based on the join condition.

Main types of JOIN:

• INNER JOIN — returns rows with matching values in both tables.
• LEFT JOIN (LEFT OUTER JOIN) — returns all rows from the left table and matching rows from the right; if there are no matches, NULLs are shown for right table columns.
• RIGHT JOIN (RIGHT OUTER JOIN) — similar to LEFT JOIN, but returns all rows from the right table.
• FULL JOIN (FULL OUTER JOIN) — returns all rows from both tables; where there are no matches, NULLs are shown in the respective columns.
• CROSS JOIN — Cartesian product of two tables, returning all possible combinations of rows.

Example of INNER JOIN:

SELECT users.name, orders.id
FROM users
JOIN orders ON users.id = orders.user_id;