Your customers live in one table and their orders live in another. That split is deliberate, and it is good design, but it means the question your manager actually asks, which customer spent the most last month, touches both tables at once. A single SELECT can only read one table. To put a customer name next to the order amount they paid, you need to stitch the two tables together on the value they share, the customer_id. That stitch is a join, and it is the skill that turns you from someone who can read a table into someone who can answer real business questions.

Part 6 gave you SELECT, WHERE and ORDER BY on a single table. A join keeps every one of those clauses exactly as you learned them and adds one idea on top: name a second table and tell the database which column links the two. Once that clicks, the rest is choosing how to handle rows that have no match on the other side, which is the whole difference between an inner join and the outer joins. Get this part right and Part 8, where you group and count, becomes easy, because grouping almost always runs on top of a join.

Why one table is never enough

Databases split information across tables on purpose, a habit called normalization, which just means you store each fact once. A customer name lives in the customers table, not repeated on every order that customer places. If Ana changes her city, you edit one row, not four hundred. The cost of that tidiness is that answering a question about orders and customers together means bringing the two back into one result, and the shared column is what makes it possible.

That shared column has names worth knowing. A primary key is the column that uniquely identifies each row of a table, so customer_id is the primary key of customers, one value per person, never repeated. A foreign key is a column in another table that points back at that key, so customer_id in the orders table is a foreign key that says which customer this order belongs to. A join matches a foreign key on one side to the primary key on the other. Here are the two tables every query below runs against.

The customers table. customer_id is the primary key, one row per customer.

The orders table. customer_id is a foreign key back to customers. Note that Sam and Mei have no orders, and order 9005 points at customer 1099, who is not in the customers table.

Your first join, INNER JOIN

The default join is the inner join, and it is the one you will write most days. It keeps only the pairs of rows that match on both sides. You name the first table in FROM, add JOIN with the second table, then ON with the condition that links them. Read it as a sentence: from customers, join orders, on the customer_id in customers equalling the customer_id in orders.

SELECT customers.name,
       orders.order_id,
       orders.amount
FROM customers
JOIN orders
  ON customers.customer_id = orders.customer_id;

Result: four rows. Ana Ruiz appears twice (orders 9001 and 9002), then Priya Nair (9003) and Diego Alonso (9004). Sam and Mei vanish because they have no orders, and order 9005 vanishes because customer 1099 does not exist.

Common failure: forgetting the ON clause, or joining on the wrong column. Without a matching condition the database pairs every customer with every order, a cross join, and five customers times five orders gives you 25 meaningless rows. If a join returns far more rows than either table holds, check your ON.

The word JOIN on its own means INNER JOIN; you can write either and get the same result. The picture below shows what the inner join is doing: it walks each order, looks up the customer whose key matches, and emits one combined row. Rows with no partner on the other side are simply left out.

Qualify columns and shorten with aliases

Both tables have a column called customer_id. When a name exists in more than one joined table, the database cannot guess which you mean, so you qualify it by writing the table name and a dot in front, as in customers.customer_id. Even when a column is unique today, qualifying every column is good style, because your query will not suddenly break the day someone adds a column with the same name to the other table.

Writing the full table name twice per column gets long, so SQL lets you give each table a short alias right after you name it in FROM. Call customers c and orders o, and now you write c.name and o.amount. The alias is just a nickname that lives for the length of the query. When the two tables join on a column with the exact same name, you can also swap the ON clause for a USING clause, which is shorter and outputs the shared column only once.

SELECT c.name, o.order_id, o.amount
FROM customers c
JOIN orders o USING (customer_id)
ORDER BY o.amount DESC;

Result: the same four matched rows as the inner join, now sorted by amount, so Priya (2000), Ana (1200), Diego (800), Ana again (450).

Common failure: USING only works when the joining column has the identical name in both tables. If one side calls it cust_id and the other customer_id, USING errors and you fall back to a full ON condition.

Keep every customer with LEFT JOIN

The inner join quietly dropped Sam and Mei because they have not ordered yet. Often that is exactly wrong. If your manager asks for a list of all customers with their order totals, the customers who bought nothing are the ones she most wants to see, because they are the ones to chase. A left join solves this. It keeps every row from the table on the left of the JOIN word, and where the right table has no match, it fills those columns with NULL, the marker for a missing value you met in Part 6.

SELECT c.name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o
  ON c.customer_id = o.customer_id
ORDER BY c.name;

Result: six rows. Ana twice, Diego, Mei with NULL order and NULL amount, Priya, and Sam with NULL order and NULL amount. Every customer appears, whether or not they ordered.

Common failure: expecting the count of a NULL column to include those empty rows. It will not. Counting o.order_id here returns 4, not 6, because COUNT skips NULLs. To count customers you count c.customer_id instead. Part 8 covers this properly.

RIGHT and FULL joins, and how to choose

A right join is the mirror of a left join. It keeps every row from the table on the right and fills the left side with NULL where there is no match. In our data a right join from customers to orders would keep order 9005, the one pointing at the missing customer 1099, and show NULL for the name. In practice most analysts rarely write RIGHT JOIN, because you can always reorder the tables and use LEFT JOIN, which reads more naturally. A full join, written FULL JOIN or FULL OUTER JOIN, keeps everything from both sides at once: matched pairs, unmatched left rows, and unmatched right rows, with NULLs wherever a side is missing. It is how you find rows that failed to line up in either direction, which is a common data-quality check.

The four join types differ only in how they treat rows that have no match. This table is the whole story, applied to our exact sample data so you can verify each count by eye.

The same two tables through four join types. The row count changes only because of how unmatched rows, Sam, Mei and order 9005, are handled.

flowchart TD
  A[Need rows from two tables] --> B{Keep rows with no match?}
  B -->|No, only matches| C[INNER JOIN]
  B -->|Keep all of one table| D[LEFT JOIN, put that table first]
  B -->|Keep unmatched from both| E[FULL JOIN]
  D --> F{Want only the unmatched?}
  F -->|Yes| G[Add WHERE key IS NULL]

Filtering an outer join, ON versus WHERE

Here is the trap that catches people who thought they understood joins. In a left join, a condition placed in the ON clause behaves differently from the same condition placed in WHERE. The database applies ON while it builds the join, deciding what counts as a match, but it applies WHERE afterward, filtering the rows that came out. With an inner join the difference does not show. With a left join it changes your answer, and quietly.

Say you want every customer alongside only their June orders. Put the date test in ON and the left join still keeps all customers, showing NULL for anyone whose orders were not in June. Move that same date test to WHERE and the NULL rows get filtered out, because NULL fails the June test, and your left join silently collapses into an inner join. You lose exactly the customers you were trying to keep.

Joining more than two tables

Real schemas have more than two tables, and joins chain. If a third table, products, held one row per item and orders carried a product_id foreign key, you would add a second JOIN with its own ON, and the database links all three in sequence. Each join narrows or widens the result by its own rule, so you build them one at a time and check the row count after each, the same layered habit from Part 6. A query that joins four tables is not four times harder; it is the same move repeated, read top to bottom.

A table can even join to itself, called a self join, which sounds odd until you meet a table that references its own key. An employees table where each row has a manager_id pointing at another employee is the classic case: join employees to employees, matching manager_id to the other copy’s employee_id, and you pair each person with their manager. You give the two copies different aliases so the database can tell them apart. You will not need self joins in your first month, but knowing the phrase means you will recognise the pattern when it appears.

Start every join as an inner, then widen

My advice for your first weeks is a simple rule: write the inner join first, confirm the matched rows look right, then decide whether you are losing rows you actually need. If you are, switch that one join to a left join and put the table you must keep on the left. Reach for right joins almost never, and full joins only when you are hunting for orphaned records on both sides. That order, inner then widen, keeps you from the most expensive join mistake, which is quietly dropping rows you did not know you dropped and reporting a total that is too low.

You can now put a customer name next to every order, keep the customers who bought nothing, and audit for records that do not line up. That is the join half of SQL. Part 8 takes these joined results and collapses them into answers: how many orders per customer, total spend per city, the running total over time, using GROUP BY, the aggregate functions, and window functions. Grouping without joining is rare, so the two parts fit together directly. If any join type here felt slippery, load the five-row sample into a sandbox and run all four, comparing the row counts against the table above until the pattern is obvious. Keep the Data Analyst guide open as your map, and revisit Part 6 if WHERE and NULL need a refresher, since both matter a lot here.

This week, take the customers and orders sample, write the inner join, then change one word to LEFT and watch Sam and Mei reappear. Then move a date filter between ON and WHERE and see the result change. Feeling that difference in your own hands is how joins stop being a diagram and start being a tool you trust.

References

• Joins Between Tables, PostgreSQL Documentation
• Table Expressions and Joined Tables, PostgreSQL Documentation

About The Author

Dr. Pranay Jha

Dr. Pranay Jha is a Cloud and AI Consultant with 18+ years of experience in hybrid cloud, virtualization, and enterprise infrastructure transformation. He specializes in VMware technologies, multi-cloud strategy, and Generative AI solutions. He holds a PhD in Computer Applications with research focused on Cloud and AI, has published multiple research papers, and has been a VMware vExpert since 2016 and a VMUG Community Leader.

See author's posts