PostgreSQL – CROSS JOIN
A CROSS JOIN, also known as a Cartesian Join, is used to combine all rows from two or more tables, producing a result set where each row from the first table is combined with every row from the second table, and so on. Unlike other types of joins, CROSS JOIN does not require a join condition, and it generates a complete combination of all rows from the involved tables.
The basic syntax of a CROSS JOIN in PostgreSQL is as follows:
SELECT column_list FROM table1 CROSS JOIN table2;Here’s a breakdown of the components:
SELECT column_list: This is the list of columns you want to retrieve from the combined result set.table1: The first table you want to cross join with another table.table2: The second table you want to cross join with the first table.
Example: Consider two tables: “students” and “courses.” Query to find all possible combinations of students and courses using CROSS JOIN is as shown below:
SELECT s.student_name, c.course_name FROM students s CROSS JOIN courses c;Above query retrieves the names of students from the “students” table and the names of courses from the “courses” table. The CROSS JOIN generates a result set where each student is combined with every course, resulting in all possible combinations.