Skip to main content
Documents
Basic SyntaxMath FunctionsDate FunctionsJSON FunctionsDatabasesTables & Schema ManagementString FunctionsTriggersIndexes

PostgreSQL CROSS JOIN

The `CROSS JOIN` in PostgreSQL produces a Cartesian product of two tables, meaning it returns all possible combinations of rows from the joined tables. This basic syntax is particularly useful when you need to combine every row of one table with every row of another.

A Cartesian product is the result of combining each row from one table with every row from another table, leading to a dataset where the number of rows is the product of the row counts of the individual tables.

Usage

The `CROSS JOIN` is used when you want to pair each row from one table with every row in another table, regardless of any matching values. This can help in scenarios where you need all possible combinations of data from two datasets.

SELECT *
FROM table1
CROSS JOIN table2;

In this syntax, `CROSS JOIN` combines each row of `table1` with every row of `table2`. The order of tables in the `CROSS JOIN` statement can affect the sequence of the resulting data, though not the content itself.

Examples

1. Basic CROSS JOIN

SELECT *
FROM products
CROSS JOIN categories;

This example generates a Cartesian product between `products` and `categories`, outputting every possible combination of product and category. If `products` has 10 rows and `categories` has 5 rows, the result set will contain 50 rows.

2. CROSS JOIN with Selected Columns

SELECT products.product_name, categories.category_name
FROM products
CROSS JOIN categories;

Here, the `CROSS JOIN` is used to combine specific columns from `products` and `categories`, providing a more focused result set. The size of the result set remains the same, but only selected columns are displayed.

3. CROSS JOIN with Additional Filters

SELECT a.name, b.course
FROM students a
CROSS JOIN courses b
WHERE b.is_active = true;

This example applies a filter after executing a `CROSS JOIN`, resulting in a Cartesian product of only active courses with all students. The result set size depends on the number of active courses.

4. CROSS JOIN with Small Datasets

SELECT x.item, y.value
FROM small_table1 x
CROSS JOIN small_table2 y;

When working with small datasets, a `CROSS JOIN` can be practical without performance concerns. For instance, combining 3 rows from `small_table1` with 4 rows from `small_table2` results in only 12 rows.

Tips and Best Practices

  • Understand the size of result sets. Since `CROSS JOIN` multiplies the number of rows, be cautious about the potential for large datasets, which can lead to performance issues.
  • Use selectively. Apply `CROSS JOIN` only when a Cartesian product is truly needed, such as generating combinations for testing or reporting purposes.
  • Combine with filters. Use `WHERE` clauses to limit the results to relevant combinations after performing a `CROSS JOIN`.
  • Consider alternatives. If specific matching criteria are needed, an `INNER JOIN` or `LEFT JOIN` might be more appropriate. For example, use an `INNER JOIN` when you need to match rows based on specific conditions, or a `LEFT JOIN` to include all rows from one table with matched rows from another.