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 the tables. If table 1 has C column and table 2 have D columns then result join table 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.
- It will return each possible combination of rows between join sets.
- The result will display using multiplication of rows from both the 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
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 very useful 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 the table1 has ABC column and table2 has 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 multiplication condition of the tables, 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.
- If 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 has no rows that output we want. In 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 very important 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.
true condition evaluation using Inner Join.
* from table1 inner join table2 ON TRUE;
Select column1, column2, … table1 inner join table2 ON TRUE;
select * from customer inner join department ON TRUE;
select cust_id, cust_name from customer inner join department ON TRUE;
We can use cross join and where condition to filter data in PostgreSQL.
Below query and syntax of where clause and cross join.
Select table1.column_name, table2.column_name, table1.column_name, table2.column_name from table1, table2 where table1.column_name = table2.column_name condition;
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 condition
- In the above query, the PostgreSQL query planner is realizing 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).
Below is the example of the cross join.
truncate table department;
select * from customer cross join department;
An example of a cross join 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.
select cust_id, cust_name from customer cross join department;
select * from customer cross join department;
It is used to generate the Cartesian product of result for two sets of the table. It is very important 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.
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 –