CROSS JOIN in PostgreSQL 🚀

A CROSS JOIN in PostgreSQL creates a Cartesian product of two tables, meaning every row from the first table is paired with every row from the second table.

1. Basic Syntax

SELECT * FROM table1CROSS JOIN table2;

• Returns all possible combinations of rows from both tables.
• If table1 has 3 rows and table2 has 4 rows, the result will have 3 × 4 = 12 rows.

2. Example: Cartesian Product of Employees and Departments

SELECT employees.name, departments.department_nameFROM employeesCROSS JOIN departments;

🔹 Every employee is paired with every department.

3. Example: Generate All Possible Date and Shift Combinations

SELECT work_dates.date, shifts.shift_nameFROM work_datesCROSS JOIN shifts;

🔹 Useful for scheduling shifts across all workdays.

4. Example: Using CROSS JOIN with a WHERE Condition

SELECT students.name, subjects.subject_nameFROM studentsCROSS JOIN subjectsWHERE subjects.subject_name IN ('Math', 'Science');

🔹 Filters the result to only Math and Science subjects.

5. Example: Using CROSS JOIN for Testing Combinations

SELECT a.color AS color1, b.color AS color2FROM colors aCROSS JOIN colors b;

🔹 Generates all possible color combinations.

6. Example: Simulating CROSS JOIN with JOIN ON TRUE

SELECT employees.name, projects.project_nameFROM employeesJOIN projects ON TRUE;

🔹 Works exactly like a CROSS JOIN.

7. Example: CROSS JOIN with Aggregation

SELECT COUNT(*) FROM studentsCROSS JOIN courses;

🔹 Counts the total number of possible student-course combinations.

8. Difference Between CROSS JOIN, INNER JOIN, and OUTER JOIN

Example Comparison

Using CROSS JOIN

SELECT employees.name, departments.department_nameFROM employeesCROSS JOIN departments;

🔹 Generates all possible employee-department pairs.

Using INNER JOIN

SELECT employees.name, departments.department_nameFROM employeesINNER JOIN departments ON employees.dept_id = departments.id;

🔹 Only matches employees to their actual department.

9. Performance Considerations

• Be cautious with large tables, as CROSS JOIN grows exponentially.
• Use LIMIT to control result size:

  SELECT * FROM table1CROSS JOIN table2LIMIT 100;
  

• Consider using filters (WHERE) to reduce output.

Next Steps

✅ Optimize CROSS JOIN queries
✅ Use CROSS JOIN in Go Fiber queries
✅ Compare CROSS JOIN with INNER JOIN for efficiency