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

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:

+----+----------+-----+-----------+----------+
| 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.

+-----+---------------------+-----------------+--------+
| 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

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

+----+----------+--------+
| 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 for 30 Days

It's easy to get started. Imagine the time you'll save by not worrying about database management. Let's do this!

PILOT FREE FOR 30 DAYS

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.