PostgreSQL Cross JoinIn 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 SyntaxThe 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:
Example of PostgreSQL Cross joinLet us see an example to understand how the PostgreSQL Cross join works: To join two tables by using PostgreSQL Cross JoinFor 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 JOINSometimes, 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 JoinGenerally, 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 ClauseIf 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 JOINIn 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: OverviewIn the PostgreSQL Cross Join section, we have learned the following topics:
Next TopicPostgreSQL Self Join |