Given a table with 10 records and another with 4 records, how many records result from a cross join?

Forty. A cross join produces the Cartesian product, pairing every row of the first table with every row of the second, so the result is 10 × 4 = 40 rows.

SELECT a., b.
FROM   table_a a
CROSS  JOIN table_b b;   -- 10 * 4 = 40 rows

The output has 40 rows and a column count equal to the sum of both tables' columns. This holds regardless of the data, because a cross join has no join condition to satisfy — that is what distinguishes it from every other join type.

The accidental cross join

Far more important than the arithmetic is recognising it when you did not intend it. Legacy comma-join syntax with a missing or misspelled predicate silently produces a Cartesian product:

-- Intended an inner join, forgot the WHERE
SELECT * FROM table_a, table_b;

This is why explicit JOIN ... ON syntax is preferred: leaving out the ON is a syntax error, so the mistake is caught rather than executed. A query that suddenly returns far more rows than either input, or that runs for hours on tables that are individually small, is very often an unintended cross join.

Why the scale is dangerous

The growth is multiplicative, not additive. Ten thousand rows crossed with ten thousand rows is 100 million rows — from two tables that each fit comfortably in memory. In a distributed engine this is worse still, because a cross join cannot be partitioned on a key and typically forces a broadcast of one side to every executor.

Legitimate uses

Cross joins are the right tool when you genuinely need every combination: building a date spine crossed with every store so reporting has a row for days with no sales, generating a full product-by-region grid before a LEFT JOIN of actuals, or creating test fixtures. In these cases one side is deliberately tiny.

In the interview, also mention that CROSS JOIN LATERAL (or CROSS APPLY) differs — the right side is correlated and evaluated per left row, so it is not a true Cartesian product.