What types of JOINs do you know? Which JOIN produces a Cartesian product?

Main types of JOIN in SQL:

• INNER JOIN — returns rows with matches in both tables.
• LEFT JOIN (LEFT OUTER JOIN) — returns all rows from the left table and matching rows from the right; if no match, NULLs are used.
• RIGHT JOIN (RIGHT OUTER JOIN) — the opposite, all from the right and matching from the left.
• FULL JOIN (FULL OUTER JOIN) — returns all rows from both tables; where there are no matches, NULLs are used.
• CROSS JOIN — returns the Cartesian product of two tables, i.e., all possible combinations of rows from the first and second tables.

The Cartesian product is specifically done by CROSS JOIN.

Example of CROSS JOIN:

SELECT * FROM TableA CROSS JOIN TableB;

If TableA has 3 rows and TableB has 4, the result will be 12 rows (3*4).