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
Syntax2
The below syntax is similar to the above syntax as we did not use the Cross Join keyword:
Syntax3
Here, we can use an INNER JOIN clause with the condition that always analyzes toward exact duplicate of the cross join:
In the above syntax’s, we have the following parameters:
| Parameter | Description |
|---|---|
| Column-lists | The column-list is used to specify the name of the column or field, which we want to return. |
| Table1 and Table2 | These are the table name from which we get the records. |
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:
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:
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):
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:
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:
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:
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.
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:
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:
After creating and inserting the values in the Fruit_sales table, we will get the following output on executing the below command:
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:
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.