PostgreSQL: Cross Join

The Cross join is a join that produces the Cartesian product of rows between two or more tables. The cross join does not have any join predicate.

If a table1 has x number of rows and a table2 has y number of rows and you perform cross join between the table1 and table2 then resultset will have x * y number of rows.

SELECT <table_1.column_name(s)>, <table_2.column_name(s)>
FROM <table_1>
CROSS JOIN <table_2>;

Consider we have Employee and Department table as bellow

Now if we take CROSS JOIN between Employee and Department tables like bellow, then every row of Employee table will be joined to each row of Department table. It does not compare any values between the two tables.

SELECT e.emp_id, e.first_name, e.last_name,
	d.dept_id, d.dept_name
FROM employee e CROSS JOIN department d
ORDER BY e.emp_id;