CROSS JOIN and SELF JOIN

CROSS JOIN Means Every Combination

CROSS JOIN has no matching predicate: each row from one input is paired with every row from the other. Its row-count formula is exact—M × N. This makes it useful for generating a designed grid and risky on large or accidental inputs.

CREATE TABLE exam_shifts (
  shift_name VARCHAR(20) PRIMARY KEY
);
INSERT INTO exam_shifts VALUES ('Morning'), ('Afternoon');

Verified Class–Shift Schedule

SELECT c.class_name, e.shift_name
FROM classes AS c
CROSS JOIN exam_shifts AS e
ORDER BY c.class_id, e.shift_name;

The alphabetical shift order places Afternoon before Morning. If the business order must be Morning then Afternoon, store a numeric shift_order column and sort by it.

Avoid Accidental Cartesian Products

-- Intent is unclear and row count multiplies:
SELECT s.student_name, c.class_name
FROM join_students AS s
CROSS JOIN classes AS c;

Five students × three classes returns 15 rows, most of which assert no real relationship. A comma-separated FROM list without a connecting predicate can create the same problem. Use explicit JOIN syntax, review the ON clause and estimate output size.

SELF JOIN Means Two Roles for One Table

SELF JOIN is a pattern, not a special keyword. The table appears twice under aliases. Each alias represents a role, such as first student and second student.

SELECT s1.student_name AS student_1,
       s2.student_name AS student_2,
       s1.class_id
FROM join_students AS s1
JOIN join_students AS s2
  ON s2.class_id = s1.class_id
 AND s1.student_id < s2.student_id
ORDER BY s1.student_id, s2.student_id;

Verified Same-Class Pairs

The equality condition requires the same non-NULL class. The strict less-than condition does two jobs: it removes Aarav–Aarav self-pairs and prevents both Aarav–Meera and Meera–Aarav from appearing. Vihaan has no class, so NULL equality does not match.

Other Useful SELF JOIN Patterns

Hierarchy

SELECT e.employee_name,
       m.employee_name AS manager_name
FROM employees AS e
LEFT JOIN employees AS m
  ON m.employee_id = e.manager_id;

LEFT JOIN preserves top-level employees whose manager_id is NULL. Other patterns include consecutive events, comparing versions, finding overlapping ranges and matching rows that share an attribute. Each requires a condition that prevents meaningless pairs.

Performance and Practice

• Index self-referencing keys such as manager_id and equality attributes such as class_id.
• Estimate pair counts; a group of N rows can create N(N−1)/2 unique unordered pairs.
• Filter each role as early as the requirement safely allows.
• Use EXPLAIN, but first verify that pair-generation logic is correct.

Practice: add an Evening shift and predict nine combinations; create three students in one class and predict three unique pairs; write a manager SELF JOIN that still displays the principal with no manager.

Official References

• MySQL 8.4 Reference Manual: JOIN Clause
• MySQL 8.4: Nested Join Optimization
• MySQL 8.4: EXPLAIN Statement

Syntax and optimizer notes were checked against the official MySQL 8.4 manual. Always test plans and row counts on your own schema and data.