PostgreSQL Cross Join
What is SQL Cross Join? How to use Cross Join?
In the last few articles, we discussed a lot about joins in SQL. Today, we are going to talk about “cross join”, another type of join in SQL.
What is CARTESIAN JOIN or CROSS JOIN in SQL?
With a Cross Join, you will see all possible conditions of the data from two or more tables. In the final result using “CROSS JOIN”, you will see a multiplicated value in the result where the rows of table A and rows of table B multiplied each other.
Let’s try understanding through a real-world example:
Suppose you are looking for a t-shirt where it comes 3 different sizes M, L, XL, and two colors - Black and Red. The prices for all three sizes are little different like for M - 10$, L - 25$, XL - 35$. Now here the Cross Join can be used if you want Black t-shirts in M-Size or Red T-shirt in M-Size. In this case, the colors combination with products and price works like a cross join.
CROSS JOIN Syntax
1 2 | SELECT crossjointable1.column1, crossjointable2.column2... FROM crossjointable1, crossjointable2 [, crossjointable3 ] |
Cross Join Syntax is really easy to understand. In the above syntax, we are selecting columns and tables from different tables and databases.
How to use Cross Join?
Below are two tables that we are gonna use to demonstrate a Cross Join.
TABLE A: BOOKCUSTOMERS
We are going to access data like name, address, salary, and age from the data given below:
1 2 3 4 5 6 7 8 9 | +----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | SAMUAL | 32 | Austin | 8000.00 | | 2 | Johnson | 25 | Ohio | 9500.00 | | 3 | Mathew | 23 | NEW York | 7000.00 | | 4 | Eva | 25 | Texas | 6500.00 | | 5 | April | 27 | San Marcos| 8500.00 | +----+----------+-----+-----------+----------+ |
TABLE B: ORDERS
In this, we have listed the people who have already ordered as given below by customers.
1 2 3 4 5 6 7 8 | +-----+---------------------+-----------------+--------+ | OID | DATE | BOOKCUSTOMER_ID | AMOUNT | +-----+---------------------+-----------------+--------+ | 102 | 2019-12-08 00:00:00 | 3 | 4000 | | 100 | 2019-09-08 00:00:00 | 3 | 3500 | | 101 | 2019-08-20 00:00:00 | 2 | 6560 | | 103 | 2018-05-20 00:00:00 | 4 | 2060 | +-----+---------------------+-----------------+--------+ |
SQL Query for Cross Join
1 2 | SQL> SELECT ID, NAME, AMOUNT FROM BOOKCUSTOMERS, ORDERS; |
As I mentioned previously, the above SQL Query is easy to understand. We have selected columns like ID, Name, Amount from both the tables. It is not needed in SQL code to add table name first and the column name.
Result
Let’s see the final output after
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | +----+----------+--------+ | ID | NAME | AMOUNT | +----+----------+--------+ | 1 | SAMUAL | 4000| | 1 | SAMUAL | 3560| | 1 | SAMUAL | 6560| | 1 | SAMUAL | 2060| | 2 | Johnson | 4000| | 2 | Johnson | 3560| | 2 | Johnson | 6560| | 2 | Johnson | 2060| | 3 | Mathew | 4000| | 3 | Mathew | 3560| | 3 | Mathew | 6560| | 3 | Mathew | 2060| | 4 | Eva | 4000| | 4 | Eva | 3560| | 4 | Eva | 6560| | 4 | Eva | 2060| | 5 | April | 4000| | 5 | April | 3560| | 5 | April | 6560| | 5 | April | 2060| +----+----------+--------+ |
Conclusion
We hope you were able to follow along and understand the Cross Join. It’s useful to finding all the possible combinations of two tables. The syntax can be a little confusing since the execution of the Cross Join does not even include the keyword ‘Join’.
We hope you’re able to use what you’ve learned here to your specific application. If you need help with setting up or managing Postgres please don’t hesitate to reach out to us at Object Rocket.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started