SQL Server Tutorialprovides basic and advanced concepts of C# for beginners and professionals.

  1. Home
  2. SQL Server Tutorial

SQL Cross Join

Back to: SQL Server Tutorial

In SQL, a CROSS JOIN is a type of join that returns the Cartesian product of two tables. This means that it combines each row from the first table with every row from the second table. The result is a new table where the number of rows is the product of the number of rows in the two tables being joined.

Syntax

SELECT * FROM table1 CROSS JOIN table2;

Example

Consider two tables:

Table1:

id name
1 Alice
2 Bob

Table2:

category value
A 10
B 20

Performing a CROSS JOIN on these tables would yield:

SELECT * FROM Table1 CROSS JOIN Table2;

Result:

id name category value
1 Alice A 10
1 Alice B 20
2 Bob A 10
2 Bob B 20

Key Points

  1. Cartesian Product: The CROSS JOIN generates a Cartesian product of the tables involved. If Table1 has m rows and Table2 has n rows, the result will have m * n rows.

  2. No Join Condition: Unlike INNER JOIN or LEFT JOIN, the CROSS JOIN does not require a condition to match rows from the tables.

  3. Potential for Large Results: Be cautious when using CROSS JOIN with large tables, as it can produce a very large number of rows and potentially impact performance.

  4. Usage: It's often used for generating combinations of data, such as creating test data or exploring all possible combinations of two sets.

Alternatives

  • INNER JOIN: If you need to combine rows based on related columns, use INNER JOIN with a condition.
  • OUTER JOIN: For combining rows with specific conditions, including unmatched rows, use LEFT JOIN, RIGHT JOIN, or FULL OUTER JOIN.
Scroll to Top