Introduction to PostgreSQL Cross Join
PostgreSQL cross join matches each row of the first table and each row of the second table. It will display all the columns of both tables. If table 1 has a C column and table 2 have D columns, then the join table’s result will have (C+D) columns. It will create a Cartesian product between two sets of data of two or multiple tables. It will not maintain any relationship between the sets of data. Instead of creating a relationship returns the result of the multiplication of two tables. It is used to join two or multiple tables.
Syntax:
- It will return each possible combination of rows between join sets.
- The result will display using the multiplication of rows from both tables.
Below is the syntax of the cross join are as follows.
SELECT column1, column2, … FROM table1 CROSS JOIN table2
Select (*) from table1 CROSS JOIN table2
Description
Given below is the description of the above syntax:
- Columns: Name of the column that is used to display the result. We can use one or multiple columns to display the result in the result set.
- Tables: Tables is used to join one or more table in single result set.
- From: From is used to display the result from the table, which we have used in the query to display results.
- Asterisk (*): Asterisk (*) is used to select all columns from selected tables.
Working of PostgreSQL Cross Join
- It will create a Cartesian product between two sets of data or two tables set of data.
- It has not maintained any relationship between the sets of data. It will return the result of data that one table will multiply with the second table.
- In PostgreSQL, cross join multiplication of two tables is also called a product because it will create a combination of rows between two joined sets.
- It is beneficial to join two or multiple tables to retrieve a single set of data.
- It will match the first table of rows with the second table of rows. Each row from the first table is matched with the second table.
- If table1 has an ABC column and table2 has a PQR column, then our resultant table of using cross join is (ABC + PQR).
- It is used to implement the Cartesian product of two or more columns.
- It will not have any matching condition in the join clause to join one or multiple tables. It will match using the tables’ multiplication condition; it will simply match one column of table1 with one column of table2.
- If we have to implement cross join in PostgreSQL of table1 with table2, every row of table 1 will match with every row of the second table.
- It consists of all columns from table1 as well as from table2. It will multiply rows from table1 and table2.
- Suppose we need to illustrate the true condition in the result set. Inner join is used.
- Below is the example of the evaluation of true condition using Inner Join in PostgreSQL cross Join.
- We cannot use PostgreSQL cross join if both tables have no rows that output we want. At this time, we need to stimulate output using full join.
- It is used to return multiple combinations of rows from two or multiple tables.
- It is used when we wish to create a comparison of every row from two tables.
- PostgreSQL cross join is work the same as SQL operator to perform Cartesian product between two different tables.
- It is a Cartesian product, so it is not allowing any condition during the result set operation. We can only restrict where condition.
- To avoid this, we can use Inner join or full join in PostgreSQL.
- It is essential to implement the result of the Cartesian product.
- Inner join is used to evaluate true condition result in PostgreSQL cross join.
- We have used customer and department table, for example, to describe cross join.
Please find below Figure1 for the same.
Example #1
true condition evaluation using Inner Join.
Syntax:
* from table1 inner join table2 ON TRUE;
Select column1, column2, … table1 inner join table2 ON TRUE;
Code:
select * from customer inner join department ON TRUE;
select cust_id, cust_name from customer inner join department ON TRUE;
Example #2
We can use cross join and where condition to filter data in PostgreSQL.
Below query and syntax of where clause and cross join.
Syntax:
Select table1.column_name, table2.column_name, table1.column_name, table2.column_name from table1, table2 where table1.column_name = table2.column_name condition;
Code:
select customer.cust_id, department.id, customer. cust_name, department.dept from customer, department where customer.cust_id = department.id and cust_id=1;
Figure 3: Example of cross join and where the condition
- In the above query, the PostgreSQL query planner realises that we are doing Inner join between the customer and department tables. But we are using cross join in the above query to fetch results between the customer and department tables.
- Cross join is implemented using both tables and has values; if one table is empty, then the result of two tables of join is also empty because it will multiply rows from the first table of rows with second tables of rows.
Example of PostgreSQL Cross Join
We can implement cross join using various conditions like (where clause).
Example
Below is an example of the cross join.
Code:
truncate table department;
select * from customer cross join department;
An example of a cross joins by using the second table has empty. In our condition, we have used the department tables that has no rows.
- In the above example, the customer table contains rows, but the department table is empty; it doesn’t contain any rows, so the result of the cross join is the empty result set.
Code:
select cust_id, cust_name from customer cross join department;
select * from customer cross join department;
Conclusion
It is used to generate the Cartesian product of result for two sets of the table. It is essential to join one or more tables in a single result set. If we need multiple combination result of two tables, then we used cross join.
Recommended Articles
This has been a guide to PostgreSQL Cross Join. Here we discussed the basic concept, description, working, and examples of PostgreSQL Cross Join. You may also have a look at the following articles to learn more –
