What is the difference between CROSS JOIN and INNER JOIN operators when joining tables in SQL?

CROSS JOIN and INNER JOIN are different types of table joins in SQL:

• CROSS JOIN (Cartesian product): returns all possible combinations of rows from both tables. If the first table has N rows and the second has M, the result will contain N×M rows. It is rarely used, except when you need to get all pairs.

• INNER JOIN: returns only those rows that have matching conditions (usually by key). If the rows do not match, they are not included in the result.

Example:

-- Table A
id | name
---|------
1  | Alice
2  | Bob

-- Table B
id | city
---|-------
1  | NY
3  | LA

-- CROSS JOIN
SELECT * FROM A CROSS JOIN B;
-- Result: 2 rows from A × 2 rows from B = 4 rows

-- INNER JOIN
SELECT * FROM A INNER JOIN B ON A.id = B.id;
-- Result: only rows with id=1, as only they match