SQL basics for business people: SELECT, WHERE, GROUP BY and JOIN

Sooner or later, the numbers you need live in a database rather than a spreadsheet. The analytics tool shows a total but not the breakdown you want, the developer is busy, and someone hands you read access and says “just query it”. This guide is for that moment.

It focuses on reading ordinary business tables, such as customers, orders and products, and on the joins you will use most. If you want the wider picture of how data models fit together, the article on AI models, data models and SQL covers that ground. Here the aim is narrower: write a query, get the right number, and know why it is right. Examples follow the PostgreSQL documentation, but the same statements work, with small differences, in most databases.

First, read the tables before you query them

Business databases usually split information across tables so each fact is stored once. A typical shop might have:

• customers: one row per customer, with customer_id, name, city and signup date.
• orders: one row per order, with order_id, customer_id, order date and status.
• order_items: one row per product line in an order, with order_id, product_id, quantity and price.
• products: one row per product, with product_id, name and category.

Before writing anything, answer two questions for each table. What does one row represent? And which column links it to the others? Most query errors come from getting the first answer wrong, for example treating order_items as if it had one row per order.

SELECT, WHERE and ORDER BY

The PostgreSQL tutorial on querying a table describes a SELECT as three parts: the columns you want, the table they come from, and optional conditions.

SELECT order_id, customer_id, order_date, status FROM orders WHERE status = 'shipped' AND order_date >= '2026-09-01' ORDER BY order_date;

• List the columns you need rather than using *. The documentation notes that SELECT * changes its results when someone adds a column to the table.
• WHERE keeps only rows where the condition is true. Combine conditions with AND, OR and NOT, and use brackets when you mix them.
• AS renames an output column, for example quantity * price AS line_value.
• Without ORDER BY, rows can come back in any order. If order matters, say so.
• DISTINCT removes duplicate rows from the result.

GROUP BY: from rows to totals

Aggregate functions such as count, sum, avg, min and max turn many rows into one number. Add GROUP BY and you get one row per group. The PostgreSQL page on aggregate functions explains the rule that confuses most beginners: WHERE filters rows before grouping, and HAVING filters groups after the totals are calculated. That is why you cannot put sum(...) inside a WHERE clause.

SELECT status, count(*) AS orders FROM orders WHERE order_date >= '2026-09-01' GROUP BY status HAVING count(*) > 10;

This counts September orders by status and shows only statuses with more than ten orders. Conditions that do not need a total belong in WHERE, which the documentation notes is also more efficient. Every column in the select list must either be in the GROUP BY or inside an aggregate.

JOIN: putting the tables back together

A join combines rows from two tables where a condition matches, usually an ID. The PostgreSQL tutorial on joins and the Wikipedia article on SQL joins describe the types you will meet:

Use table aliases and qualify every column, as the PostgreSQL tutorial recommends, so the query does not break if both tables later gain a column with the same name:

SELECT c.name, o.order_id, o.order_date FROM customers c JOIN orders o ON o.customer_id = c.customer_id;

Two behaviours catch people out. First, an inner join drops rows that have no match. Wikipedia notes that NULL never matches anything, not even another NULL, so inner joins on columns that can be empty silently lose data. A LEFT JOIN is often safer. Second, every matching pair of rows produces a result row, so joining orders to order_items gives one row per item. Count orders after that join and you count items instead.

A worked example (illustration only)

The founder of an online tea shop wants September revenue by product category, and the number of customers who signed up in September but have not ordered yet.

Revenue by category joins three tables and groups the result:

SELECT p.category, sum(i.quantity * i.price) AS revenue, count(DISTINCT o.order_id) AS orders FROM orders o JOIN order_items i ON i.order_id = o.order_id JOIN products p ON p.product_id = i.product_id WHERE o.status = 'shipped' AND o.order_date >= '2026-09-01' AND o.order_date < '2026-10-01' GROUP BY p.category ORDER BY revenue DESC;

Note count(DISTINCT o.order_id). Because the join produces one row per item, a plain count would overstate orders.

Customers with no orders need a LEFT JOIN and a test for NULL:

SELECT c.customer_id, c.name FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id WHERE c.signup_date >= '2026-09-01' AND c.signup_date < '2026-10-01' AND o.order_id IS NULL;

Every September customer is kept, and those with no matching order show NULL in the order columns, which the last condition picks out.

Common mistakes and when to ask for help

• Not knowing what one row represents, then summing at the wrong level.
• Counting rows after a one-to-many join instead of counting distinct IDs.
• Using an inner join and losing customers or products with no match.
• Writing = NULL instead of IS NULL.
• Using “between” dates that miss the last day; use greater than or equal to the start and less than the next period.
• Forgetting cancelled or test orders that the status column would exclude.

Check every new query against a number you trust, such as a total from the finance system. Ask a data colleague when the tables have unclear names, when a “simple” metric has an agreed definition you do not know, or when your query runs for minutes on a production database. Read access is a privilege, and a slow query can affect the system other people rely on.