Comparative Study of Join Algorithms in MySQL: Cross, Inner, Outer, and Self Joins

Abstract

A Structured Query Language (SQL) joins are fundamental to relational database operations, merging data from multiple tables into coherent result sets. This comparative study examines the performance characteristics of cross joins, inner joins, left/right/full outer joins, and self‑joins in MySQL under large‑scale workloads. Experiments were conducted on datasets ranging from 10,000 to 50 million rows using execution time, CPU utilization, memory consumption, and scalability as metrics. Results demonstrate inner joins achieve superior performance when properly indexed; outer joins incur moderate overhead for handling unmatched rows; self‑joins perform comparably to inner joins with effective indexing but degrade under multi‑level recursion; cross joins exhibit exponential growth and should be used sparingly. Recommendations for index strategies and query design are provided.