Databases are an important aspect in the computing domain to manage raw data by storing it in the form of tables. Once the data is sorted and placed properly, gathering information and generating better decisions becomes useful and efficient. PostgreSQL databases allow the use of JOINs to join multiple tables and generate results accessed from multiple places.

This guide will demonstrate how to use CROSS JOIN in PostgreSQL.

How to Use CROSS JOIN in PostgreSQL?

CROSS JOIN in PostgreSQL returns a paired combination of every row available on the first table and every row on the second. If Table A contains three rows and the second table contains the same number of rows. Applying CROSS JOIN will create a match of the first row with respect to the second to generate three matches in this situation and so on for other rows of the first table:

Syntax With CROSS JOIN

The following syntax simply takes a list of columns from the first table and uses the CROSS JOIN condition on the second table:

SELECT <Col_list>
 FROM <Tab_A>
 CROSS JOIN <Tab_B>;

Syntax Without CROSS JOIN

The following syntax also generates the same results as the above query but without the use of the CROSS JOIN keyword:

SELECT <Col_list>
 FROM <Tab_A>,   <Tab_B>;

Syntax Using INNER JOIN as CROSS JOIN

INNER JOIN can also be used to get the results of the CROSS JOIN by using the following syntax. It takes columns of the first table and applies INNER JOIN with ON condition on the second table. It generates CROSS JOIN results when the condition is true for the second table:

SELECT *
 FROM Tab_A
 INNER JOIN Tab_B ON true;

Example 1: Combine Tables With CROSS JOIN

Start the example by using the following command to get the data from the colors table:

SELECT * FROM colors;

Running the above code will return the data from the colors table:

Use the same query with the sizes table as written below:

SELECT * FROM sizes;

The following screenshot displays the data from the sizes table:

Use the following CROSS JOIN query to join both tables:

SELECT * FROM colors
 CROSS JOIN sizes;

Running the above query will match each record from the first table with the second table and generate the following result. It matches records of colors with sizes as it takes red and matches with all the sizes available on the table and so on for blue and green:

Example 2: Without Using CROSS JOIN

The following query does not use the CROSS JOIN keyword in the query but still produces the same result:

SELECT * 
 FROM colors, sizes;

It simply takes all the records from both tables and matches for their records which basically is the working of CROSS JOIN:

Example 3: Apply CROSS JOIN With INNER JOIN

Another method to apply CROSS JOIN in PostgreSQL is using the INNER JOIN as mentioned in the following query:

SELECT *
 FROM colors
 INNER JOIN sizes ON true;

This code contains the following:

- It selects all the records from the colors table and applies INNER JOIN with the sizes table.
- The ON condition checks for each true record and joins both tables as a CROSS JOIN:

That’s all about using CROSS JOIN in PostgreSQL.

Conclusion

The CROSS JOIN in PostgreSQL databases generates a cartesian product of two tables and joins them to produce one table. PostgreSQL allows the user to apply CROSS JOIN with multiple syntaxes such as using the CROSS JOIN keyword, without using CROSS JOIN, and using INNER JOIN with the condition. It matches each record of the first table with each record of the second table and creates another as this guide demonstrated in detail.