Deep Dive into SQL Joins and Indexes
Learn the differences between SQL joins (INNER, LEFT, RIGHT, FULL) and how database indexes speed up operations.
Understanding SQL Joins
Joins combine rows from two or more tables based on a related column between them:
• INNER JOIN: Returns records that have matching values in both tables. • LEFT JOIN: Returns all records from the left table, and matching records from the right table. Fill missing values with NULL. • RIGHT JOIN: Returns all records from the right table, and matching records from the left table. • FULL JOIN: Returns all records when there is a match in either left or right table.
SQL Join Syntax
SELECT
students.StudentName,
enrollments.CourseName
FROM Students students
INNER JOIN Enrollments enrollments
ON students.StudentID = enrollments.StudentID;How Indexes Speed Up Queries
Without indexes, databases perform a sequential scan (table scan), checking every single row, which is an O(N) operation.
An index is a separate data structure (usually a B-Tree or Hash Table) that stores pointers to rows sorted by the indexed column, enabling O(log N) searches.
However, indexing has tradeoffs:
- Speeds up READS (SELECT queries with filters).
- Slows down WRITES (INSERT, UPDATE, DELETE) since the index structure must be modified as well.
- Consumes extra storage space.