Beyond the standard joins, explain the purpose and provide practical use cases for both a `CROSS JOIN` and a `SELF JOIN` in SQL. Illustrate with examples.
Node.js interview question for Advanced practice.
Answer
While INNER and OUTER joins are most common, CROSS JOIN and SELF JOIN serve specific, important purposes. CROSS JOIN: Produces the Cartesian product of two tables, meaning it pairs every row from the first table with every row from the second table. It's used when you need to generate all possible combinations of records. Use Case: Generating a master list of all possible combinations. For example, if you have a Sizes table (S, M, L) and a Colors table (Red, Green), a CROSS JOIN will produce all 6 possible product variants (S-Red, S-Green, M-Red, M-Green, L-Red, L-Green). sql SELECT s.sizename, c.colorname FROM Sizes s CROSS JOIN Colors c; SELF JOIN: This isn't a special type of join, but rather a regular join where a table is joined with itself. It's used to query hierarchical data or compare rows within the same table. Use Case: Finding employees who report to a specific manager from an Employees table that has employeeid and managerid columns. You join the table to itself, aliasing it to treat it as two separate tables: one for the employee and one for the manager. sql SELECT e.employeename, m.employeename AS managername FROM Employees e INNER JOIN Employees m ON e.managerid = m.employeeid;
Explanation
A CROSS JOIN is equivalent to an INNER JOIN where the join condition always evaluates to true, or simply listing tables in the FROM clause with a comma in older SQL syntax.