Cross Join in Sql with examples

by Yogi P - December 4, 2022

Cross Join in Sql

In SQL, a CROSS JOIN is a type of join that returns all possible combinations of rows from the tables being joined. It is sometimes called a cartesian join because it produces a result set that is the cartesian product of the sets of rows from the joined tables. For example, if table A has 3 rows and table B has 2 rows, a CROSS JOIN of these tables will return 6 rows (3 x 2 = 6).

The syntax for a CROSS JOIN in SQL is:

SELECT *
FROM table1
CROSS JOIN table2;

This will return all rows from table1 combined with all rows from table2. Note that if both tables have a column with the same name, the resulting table will have two columns with the same name, which may cause ambiguity in your query. In this case, you can use table aliases to specify which table each column belongs to. For example:

SELECT table1.col1, table1.col2, table2.col1, table2.col2
FROM table1
CROSS JOIN table2;

Here, we are selecting specific columns from each table and using table aliases (table1 and table2) to avoid ambiguity.

A CROSS JOIN in SQL is a useful way to generate a large number of rows quickly, but it can also be very inefficient if one of the tables being joined is very large. In general, it is better to use a CROSS JOIN only when you know that the tables being joined are small, or when you have some other way of limiting the number of rows in the result set (e.g. using a WHERE clause).

Here are some examples of using CROSS JOIN in SQL:

Suppose we have two tables, students and courses, with the following data:

students:
+—-+——-+
| id | name |
+—-+——-+
| 1 | Alice |
| 2 | Bob |
| 3 | Carol |
+—-+——-+

courses:
+—-+—————-+
| id | course_name |
+—-+—————-+
| 1 | Math |
| 2 | English |
| 3 | Computer Science |
+—-+—————-+

If we want to see all the possible combinations of students and courses, we can use a CROSS JOIN:

SELECT *
FROM students
CROSS JOIN courses;

This would produce the following result: