A SQL join combines rows from two or more tables based on a related column, usually a key. The main types are INNER JOIN (only matching rows), LEFT JOIN (all rows from the left table plus matches), RIGHT JOIN (all rows from the right table plus matches), FULL OUTER JOIN (all rows from both sides), and CROSS JOIN (every possible combination). Picking the right type is less about memorizing definitions and more about being clear on what question you are asking of the data.

Conceptually, a join pairs rows from one table with rows from another using the join condition (like matching IDs), and then your chosen join type decides what happens to rows that do not match. Databases use indexes and optimization under the hood, but thinking in terms of "which rows match, and what happens to unmatched rows" makes join behavior predictable. Most cases of a join "running fine" but returning wrong numbers come from skipping this reasoning step.

Chain them: join the first two tables with a JOIN and an ON condition, then add another JOIN and ON for the third table, making sure each condition links on a real key. Watch out for two things: with outer joins, the order of joins affects intermediate results, and every extra table raises the risk of row multiplication if one row matches multiple rows. Check row counts before and after each join to confirm you have not duplicated or lost records.

Skip the abstract A/B/C tables. Load a small multi-table dataset — orders, customers, products, payments — and answer business questions like revenue per customer, products never ordered, or customers with no payments in the last 90 days. Learning SQL joins with examples that mimic real analytical problems builds the reasoning that generic tutorials never develop, because you have to predict the output before running the query.

Look for question sets that give you a table schema, a question, and the expected output rather than just answers to read. A good progression is: two-table joins, then multi-table joins with aggregation, then self-joins for problems like finding duplicates. The key habit is predicting the result row by row before executing, then comparing — that is what converts practice into skill instead of pattern memorization.

Frequently asked ones include: explain the join types with an example, join three tables and filter the result, find rows in one table with no match in another (LEFT JOIN plus a NULL check), find duplicates using a self-join, and join tables before applying GROUP BY for aggregates. Interviewers often add a twist like duplicate keys causing inflated sums, so practice explaining why row counts change after each join.

It is a useful starting point but misleading if you stop there. Venn diagrams show set membership, while joins match rows on keys — so they cannot show row multiplication, how WHERE and ON filter differently in outer joins, or why a LEFT JOIN plus aggregation can produce wrong totals. Use the SQL joins Venn diagram as a memory aid for which rows survive, then verify your understanding with row counts on real queries.

A subquery is a query nested inside another query, and it can appear in the SELECT, FROM, or WHERE clause. You use one when you need a value computed before the main query runs — for example, finding customers whose spend is above average, where the average is calculated by the inner query. A helpful mental model: a subquery is a query whose result (a single value, a list, or a table) becomes input for the outer query.

In the WHERE clause, use a subquery to filter by a computed condition, such as amounts greater than the average, or with IN and EXISTS. In the FROM clause, treat the subquery result as a derived table and give it an alias so you can aggregate or join on it. In the SELECT clause, a subquery returns a value per row, which can get expensive. A reliable habit: write and test the inner query first, confirm its output, then wrap it in the outer query.

Correlated subqueries in SQL reference a column from the outer query, so the inner query is logically evaluated once for every row of the outer query. That per-row execution is why they slow down on large tables. They are sometimes unavoidable and sometimes the optimizer can rewrite them internally, but a slow correlated subquery can often be replaced with a JOIN or a window function — so always check the execution plan before accepting the slowdown.

Because some questions are naturally two-step: compare each row against an aggregate (above-average spend), check for existence (EXISTS), or compute a value per group before filtering. Doing the same with a join often multiplies rows and forces extra GROUP BY logic. Subqueries are used in SQL because they express the intent more directly, which means fewer mistakes. The practical rule: write whichever version expresses the question most clearly, then optimize if performance demands it.

Choose sets that progress in difficulty: single-value subqueries, then multi-row filters with IN and EXISTS, then correlated subqueries, then subqueries in the FROM clause combined with aggregation. The answers matter less than the method — predict the output first, run the query, and investigate any gap between what you expected and what you got. That comparison is where the real learning about subquery behavior happens.