DatabasesInterview questions and answers

A JOIN combines rows from two or more tables based on a related column. Types: INNER (only matches), LEFT (all rows from left, NULL where no match on right), RIGHT (mirror), FULL OUTER (everything from both sides), and CROSS (every combination). The right join is foundational to relational thinking.

INNER JOIN keeps only rows that have a match on both sides — like an intersection. LEFT JOIN keeps every row from the left table, filling NULL for missing matches on the right — useful when you want a list with optional related data (e.g. users and their last order, where some users have no orders).

An index is a separate data structure (usually a B-tree) that speeds up lookups by key. It's like the index at the back of a book — instead of scanning every page, you jump straight to where you need. Indexes speed reads but slightly slow writes (the index also needs updating).

A composite index covers multiple columns in a specific order, e.g. (user_id, created_at). The order matters: queries that filter on user_id can use the index; ones filtering only on created_at can't. Always lead with the most selective column you query equality on.

A covering index contains every column a query needs, so the database can answer the query from the index alone without touching the table itself. Massive speedup for hot read paths. In Postgres, use INCLUDE to add 'payload' columns to an index without making it part of the key.