PostgreSQL Cross Join returns each row from 1st table joins with all the rows of another table. If 1st table contains x rows and y rows in 2nd table, then result set will be x*y rows. Cross join is also called as Cartesian join.
Following is the ven-diagram for PostgreSQL Cross Join.
PostgreSQL Cross Join Examples:
Tables used for below examples:
emp table:
dept table:
deptno | dname | loc --------+------------+---------- 40 | OPERATIONS | BOSTON 30 | SALES | CHICAGO 10 | ACCOUNTING | NEW YORK 20 | RESEARCH | DALLAS
1. Following query will get all macthing rows with 2 tables.
select e.empno,e.ename,e.sal,d.dname,d.loc from emp e, dept d;
Output:
empno | ename | sal | dname | loc -------+--------+---------+------------+---------- 7521 | WARD | 1250.00 | OPERATIONS | BOSTON 7369 | SMITH | 1300.00 | OPERATIONS | BOSTON 7654 | MARTIN | 1250.00 | OPERATIONS | BOSTON 7934 | MILLER | 1800.00 | OPERATIONS | BOSTON 7566 | JONES | 2975.00 | OPERATIONS | BOSTON 7499 | ALLEN | 1600.00 | OPERATIONS | BOSTON 7698 | BLAKE | 2850.00 | OPERATIONS | BOSTON 7521 | WARD | 1250.00 | SALES | CHICAGO 7369 | SMITH | 1300.00 | SALES | CHICAGO 7654 | MARTIN | 1250.00 | SALES | CHICAGO 7934 | MILLER | 1800.00 | SALES | CHICAGO 7566 | JONES | 2975.00 | SALES | CHICAGO 7499 | ALLEN | 1600.00 | SALES | CHICAGO 7698 | BLAKE | 2850.00 | SALES | CHICAGO 7521 | WARD | 1250.00 | ACCOUNTING | NEW YORK 7369 | SMITH | 1300.00 | ACCOUNTING | NEW YORK 7654 | MARTIN | 1250.00 | ACCOUNTING | NEW YORK 7934 | MILLER | 1800.00 | ACCOUNTING | NEW YORK 7566 | JONES | 2975.00 | ACCOUNTING | NEW YORK 7499 | ALLEN | 1600.00 | ACCOUNTING | NEW YORK 7698 | BLAKE | 2850.00 | ACCOUNTING | NEW YORK 7521 | WARD | 1250.00 | RESEARCH | DALLAS 7369 | SMITH | 1300.00 | RESEARCH | DALLAS 7654 | MARTIN | 1250.00 | RESEARCH | DALLAS 7934 | MILLER | 1800.00 | RESEARCH | DALLAS 7566 | JONES | 2975.00 | RESEARCH | DALLAS 7499 | ALLEN | 1600.00 | RESEARCH | DALLAS 7698 | BLAKE | 2850.00 | RESEARCH | DALLAS