CodesJava

Easy learning with example program codes

CROSS Join in PostgreSQL

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:

DEPT_ID	  DEPT_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	        75
2	B	70	2	Sales	        75
3	C	65	2	Sales	        75
4	D	80	2	Sales	        75
5	E	78	2	Sales	        75
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.

Please follow and like us:
error









Copyright © 2019 CodesJava DMCA.com Protection Status