self joins types and usage in what scenarios?

Question

Accepted Answer

Self joins are used to join a table to itself. Types include inner, outer, and self-join.

Self joins are used when a table needs to be joined with itself.
Inner self join returns only the matching rows between the two instances of the table.
Outer self join returns all rows from both instances of the table, with null values where there is no match.
Self join can be used to find hierarchical relationships within a table.
Example: Employee table with columns employee_id and manager_id can be self-joined to find the hierarchy of employees.
Example: Product table with columns product_id and parent_product_id can be self-joined to find the hierarchy of products.

Top AEGIS BUSINESS BI Developer interview questions & answers