CROSS JOIN
The PostgreSQL CROSS Join query combines each row of the first table with each row of the second table in the result set. Thus, if we select all the fields of both the table than the resultant table contains x*y rows, where the FIRST table has x number of rows and the Second Table has y number of rows.
Syntax:
SELECT columns FROM table_1 CROSS JOIN table_2;
Example:
Employment Table:
| ID | STATE | RATE |
| 1 | A | 60 |
| 2 | B | 70 |
| 3 | C | 65 |
| 4 | D | 80 |
| 5 | E | 78 |
Department Table:
| ID | NAME | PERCENT |
| 1 | IT | 60 |
| 2 | SALES | 75 |
| 3 | BANK | 50 |
Query:
SELECT * FROM “EMPLOYMENT” CROSS JOIN “DEPARTMENT”; |
Output:
| ID | STATE | RATE | DEPT_ID | DEPT_NAME | PERCENT |
| 1 | A | 60 | 1 | IT | 60 |
| 2 | B | 70 | 1 | IT | 60 |
| 3 | C | 65 | 1 | IT | 60 |
| 4 | D | 80 | 1 | IT | 60 |
| 5 | E | 78 | 1 | IT | 60 |
| 1 | A | 60 | 2 | Sales | 80 |
| 2 | B | 70 | 2 | Sales | 80 |
| 3 | C | 65 | 2 | Sales | 80 |
| 4 | D | 80 | 2 | Sales | 80 |
| 5 | E | 78 | 2 | Sales | 80 |
| 1 | A | 60 | 3 | Bank | 50 |
| 2 | B | 70 | 3 | Bank | 50 |
| 3 | C | 65 | 3 | Bank | 50 |
| 4 | D | 80 | 3 | Bank | 50 |
| 5 | E | 78 | 3 | Bank | 50 |
Explanation:
The EMPLOYMENT and the DEPARTMENT are the already existing tables that are joined with CROSS JOIN query so that every row from the EMPLOYMENT table ia matched with every row from the DEPARTMENT table thus producing a 5*3 table, where 5 is the number of rows in the EMPLOYMENT table and 3 is the number of rows in the DEPARTMENT table.