What is a CROSS JOIN and how does it differ from an INNER JOIN?

Question

Accepted Answer

39
   What is a CROSS JOIN and how does it differ from an INNER JOIN?
  Level: Intermediate — Tests your understanding of Cartesian products and join conditions.

  A CROSS JOIN returns the Cartesian product of two tables — it pairs every row of the first table with every row of the second table. There is no join condition.

  If table A has m rows and table B has n rows, a CROSS JOIN produces m × n rows.

  $$ total\_rows = m 	imes n $$

  
     Key difference: An INNER JOIN uses a condition (ON) to return only matching rows, while a CROSS JOIN has no condition and returns all possible combinations. In fact, an INNER JOIN is essentially a CROSS JOIN filtered by an ON clause.
  

   Side-by-Side Comparison
  
    
      
        
          Feature
           CROSS JOIN
           INNER JOIN
        
      
      
        
          Join condition (ON)
          None
          Required
        
        
          Result
          Cartesian product (m × n)
          Only matching rows
        
        
          Row count
          Very large
          Depends on matches
        
        
          Typical use
          Generate combinations
          Combine related data
        
      
    
  

   When to Use CROSS JOIN
  
     Generating all combinations (e.g., sizes × colors for a product catalog).
     Creating date/number series or grids for reports.
     Producing test data or a matrix of possibilities.
  

  
     Warning: A CROSS JOIN on two large tables can explode row counts (e.g., 10,000 × 10,000 = 100 million rows), causing severe performance issues. Use it deliberately, and often with small lookup tables.
  

   Quick Example
-- Sample: sizes(3 rows) and colors(4 rows)
-- CROSS JOIN produces 3 x 4 = 12 combinations
SELECT s.size, c.color
FROM sizes s
CROSS JOIN colors c;

-- Practical: every product variant (size + color)
SELECT p.product_name, s.size, c.color
FROM products p
CROSS JOIN sizes s
CROSS JOIN colors c;

-- Compare: INNER JOIN needs a condition
SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;  -- filtered by match

  
     Interviewer tip: The one-liner they want — "A CROSS JOIN returns the Cartesian product (every row paired with every row) with no condition, while an INNER JOIN uses an ON clause to return only matching rows."

Feature	CROSS JOIN	INNER JOIN
Join condition (ON)	None	Required
Result	Cartesian product (m × n)	Only matching rows
Row count	Very large	Depends on matches
Typical use	Generate combinations	Combine related data

What is a CROSS JOIN and how does it differ from an INNER JOIN?

Answer with Explanation

What is a CROSS JOIN and how does it differ from an INNER JOIN?

Side-by-Side Comparison

When to Use CROSS JOIN

Quick Example