PostgreSQL Cross Join

In this section, we are going to understand the working of PostgreSQL Cross join, which allows us to create a Cartesian Product of rows in two or more tables. We also learn how to use table-aliasing, WHERE clause, and join multiple tables with the help of the PostgreSQL Cross Join clause.

What is PostgreSQL Cross Join?

The PostgreSQL Cross Join is used to combine all possibilities of the multiple tables and returns the output, which contain each row from all the selected tables. The CROSS JOIN, further known as CARTESIAN JOIN that allows us to produce the Cartesian product of all related tables.

The Cartesian product can be described as all existing rows in the first table multiplied by all rows in the second table. It is parallel to the Inner Join, where the join condition is not existing with this clause.

The following Venn diagram displays the PostgreSQL Cross Join, where we can easily understand that the Cross Join returns all the records from Table1 and Table2, and each row is the grouping of rows from both tables.

PostgreSQL Cross Join Syntax

The Cross-Join keyword is used with the SELECT command and must be written after the FROM Keyword. The below syntaxes are used to get all the data from both associated tables:

Syntax1

SELECT column-lists  
FROM Table1  
CROSS JOIN Table2;  

Syntax2

The below syntax is similar to the above syntax as we did not use the Cross Join keyword:

SELECT [column_list|*]
FROM Table1, Table2;

Syntax3

Here, we can use an INNER JOIN clause with the condition that always analyzes toward exact duplicate of the cross join:

SELECT *
FROM Table1
INNER JOIN Table2 ON true;

In the above syntax's, we have the following parameters:

Example of PostgreSQL Cross join

Let us see an example to understand how the PostgreSQL Cross join works:

To join two tables by using PostgreSQL Cross Join

For this, we will use the Summer_fruits and Winter_fruits table, which we created in the PostgreSQL Full join section of the PostgreSQL tutorial.

Table1: Summer_fruits

To see the Summer_fruits table's records, we will use the SELECT command as we can see in the following command:

Select * from Summer_fruits;

Output

After executing the above command, we will get the data from the Summer_fruits table:

Table2: Winter_fruits

To see the records from the Winter_fruits table, we will use the SELECT command as we can see in the following command:

Select * from Winter_fruits;

Output

After executing the above command, we will get the data from the Winter_fruits table:

We will execute the below command to get all records from both tables (Summer_fruits and Winter_fruits):

SELECT *
FROM Summer_fruits 
CROSS JOIN Winter_fruits ;

Output

On executing the above command, we will get the following output:

When the CROSS-JOIN command is executed, we will see that it shows 42 rows, which implies that the Six rows from the Summer_fruites table multiply by the Seven rows from the Winter_fruits table.

Note: It is suggested to use separate column names in its place of SELECT * command to avoid the output of repetitive columns two times.

Uncertain Columns problem in PostgreSQL CROSS JOIN

Sometimes, we need to get the selected column records from more than two tables. And these tables can have some matching column names.

Let see one example to understand this type of case, suppose the Summer_fruits and Winter_fruits table contain one similar column that's is: fruit_id as we can see in the below command:

SELECT fruit_id, SF_ID, Summer_fruits_name, WF_ID, Winter_fruits_name
FROM Summer_fruits 
CROSS JOIN Winter_fruits;

Output

On executing the above command, the PostgreSQL CROSS JOIN command throws an error, which is The column name is ambiguous, and it implies that the name of the column exists in both tables. PostgreSQL becomes unclear about which column we want to display.

Therefore, to solve the above error, we will specify the table name before the column name as we can see in the below command:

SELECT Summer_fruits.fruit_id, Summer_fruits.SF_ID, Summer_fruits.Summer_fruits_name, 
Winter_fruits.WF_ID, Winter_fruits.Winter_fruits_name
FROM Summer_fruits 
CROSS JOIN Winter_fruits ;

Output

After executing the above command, we will get the below result:

Table-aliasing with PostgreSQL Cross Join

Generally, the tables we want to join will have columns with a similar name like the fruit_id column.

Instead of using the complete table name, we can use table aliases to assign the joined tables short names to make the command more understandable. Sometimes, writing a full table name is a tedious process.

Thus, we will use the table aliasing, and it returns a similar outcome as above as we can see in the below command:

SELECT s.fruit_id, s.SF_ID, s.Summer_fruits_name, w.WF_ID,
w.Winter_fruits_name
FROM Summer_fruits s
CROSS JOIN Winter_fruits w;

Output

Once we implemented the above command, we will get the below output:

PostgreSQL Cross Join using WHERE Clause

If we want to identify the rows from Table1 (Summer_fruits) that do not have any matching rows in Table2 (Winter_fruits), we can use the WHERE condition with the Cross Join.

As we can see in the below command, we are selecting the rows from both tables Summer_fruits and Winter_fruits where Summer_fruits_name is equal to Watermelon and Winter_fruits_name is not equal to Pineapple.

SELECT Summer_fruits.fruit_id, Summer_fruits.SF_ID, 
Summer_fruits.Summer_fruits_name, Winter_fruits.WF_ID,
Winter_fruits.Winter_fruits_name
FROM Summer_fruits 
CROSS JOIN Winter_fruits  
WHERE Summer_fruits_name ='Watermelon' 
AND Winter_fruits_name != 'Pineapple';

Output

On executing the above command, we will get the following result:

To join multiple tables using PostgreSQL Cross JOIN

In the above section, we have two tables as Summer_fruits and Winter_fruits now, if we want to join more than two tables and get the records from that particular table. In that case, we will use the Cross join.

For example, we will create Fruite_sales table by using Create Clause as we can see in the following command:

CREATE TABLE Fruit_sales (
Fruit_id int primary key, 
Sales_id int,
Fruits_name varchar not null
);

To see the Fruit_sales table's values, we will use the SELECT clause as follows:

Once the Fruit_sales table has been created successfully, we will insert some values into it with the help of INSERT command as we can see in the following command:

INSERT INTO Fruit_sales (fruit_id, Sales_id, Fruits_name)
VALUES (1, 101,'Apple'),
(2, 102,'Banana'),
(3, 103,'Watermelon'),
(4, 104,'Mango'),
(5, 105,'Pineapple'),
(6, 105,'Grapes');

After creating and inserting the values in the Fruit_sales table, we will get the following output on executing the below command:

Select * from Fruit_sales;

Table3: Fruit_sales

Now, we will join multiple tables such as Summer_fruits, Winter_fruits, and Fruit_sales with the help of PostgreSQL Cross Join as we can see in the following statement:

SELECT * FROM Summer_fruits  
LEFT JOIN (Winter_fruits CROSS JOIN Fruit_sales)  
ON Summer_fruits.fruit_id= Fruit_sales.fruit_id  
ORDER BY Fruits_name;  

Output

On executing the above command, we will get the following output:

Overview

In the PostgreSQL Cross Join section, we have learned the following topics:

• We used the PostgreSQL Cross join clause to select data from two tables and understand the Ambiguous Columns problem.
• We used the PostgreSQL Full join condition with table aliasing and WHERE clause and fetching records from multiples tables.