SQL queries: selecting, joining and checking data

SQL queries let you ask a relational database a precise question. The difficult part is often deciding what one result row represents and how records in different tables relate. A query can run without errors and still answer the wrong question: an unintended join may multiply sales, a filter may remove missing records, or an unordered result may be treated as a ranking. A reliable workflow begins with the business question, makes table relationships explicit, and checks the returned rows against small examples.

Consider a library with members(member_id, name) and loans(loan_id, member_id, loan_date, returned_at). An analyst wants the number of loans per member in a given month. Before writing SQL, decide whether members with no loans should appear and whether a renewal counts as a separate loan. Those choices determine the join and the counting rule. Syntax follows the definition, not the other way round.

Start SQL queries with a row-level question

Write down the intended unit of analysis: one row per loan, one row per member, or one row per member and month. List the columns needed to answer the question and the records that should be included. Selecting every column with SELECT * may help inspect a small table, but a production query benefits from naming the needed columns explicitly. It is then easier to see what the result means and less likely that an added table column will affect downstream work unexpectedly.

For a simple inspection, SELECT loan_id, member_id, loan_date FROM loans WHERE loan_date >= DATE '2026-01-01'; asks for loans on or after a date. A second condition can establish an exclusive upper bound, such as loan_date < DATE '2026-02-01'. That pattern is useful when a timestamp includes hours and minutes: testing equality with a date may omit records later in the day. Check the database’s date types and time-zone conventions before assuming that a boundary represents the same moment for every user.

Filtering happens before many later calculations. If the question concerns all library members, filtering a joined loan table in the WHERE clause can unexpectedly remove members with no matching loan. The location of a condition matters as much as the condition itself.

Make SQL joins follow the real table relationship

A join connects records according to a stated condition. The loans.member_id value refers to members.member_id; matching names would be unreliable because names can repeat and change. An inner join returns combinations with a match on both sides. A left join keeps each member even when no matching loan exists, supplying null values for the loan columns in that case.

Suppose each member has several loans. Joining members to loans produces several rows for that member, which is correct for a list of loans but wrong if someone later counts joined rows as members. Before aggregating, check whether the key on each side is unique and whether the relationship is one-to-one, one-to-many, or many-to-many. A join to another one-to-many table, such as payments, can multiply rows again. In that situation, aggregate each source to the desired grain before combining results or use an explicitly justified distinct count.

Use aliases to make the relationship legible: FROM members AS m LEFT JOIN loans AS l ON l.member_id = m.member_id. The alias is a readability tool, not a substitute for a correct condition. A join that omits the relationship may return every possible pairing; a plausible-looking total from that output should not be trusted.

Aggregate without changing the question

For the library’s monthly count, a left join can keep members with no monthly loans when the date condition is part of the join: LEFT JOIN loans AS l ON l.member_id = m.member_id AND l.loan_date >= DATE '2026-01-01' AND l.loan_date < DATE '2026-02-01'. Then select m.member_id, m.name, and COUNT(l.loan_id), grouped by the member columns. Counting l.loan_id yields zero when the member has no matching loan; counting * would count the preserved member row and yield one.

Grouping defines the unit of the result. If two members share a name, grouping only by name combines them incorrectly. Include the stable member identifier. If a reporting period is also selected, group at that period’s level deliberately. Do not use a sum or average until the units and null-handling behavior are clear.

An optional HAVING COUNT(l.loan_id) > 3 filters groups after aggregation. A WHERE condition filters individual rows before grouping. Mixing those steps can produce a valid query with a different meaning. When the query grows complex, write a small intermediate query or common table expression and inspect its row count before building the final report.

Check nulls, duplicates and ordering

Null represents an unknown or absent value in many SQL expressions; it does not compare like an ordinary string or number. To find unreturned loans in this example, use returned_at IS NULL. To count returned loans, decide how null should be treated and whether a late return changes the period being reported. An INNER JOIN can silently discard unmatched records, so compare its counts with source-table counts when completeness matters.

DISTINCT removes duplicate result rows, but it can conceal a flawed join. Investigate why duplicates arose before applying it. Inspect several known members: one with zero loans, one with one, one with many, and one with records on the date boundaries. These test cases often reveal mistakes that a large aggregate hides.

Add ORDER BY when result order matters. A database does not promise a stable presentation order merely because results appeared sorted during a trial. A top-ten list also needs a tie rule: ORDER BY loan_count DESC, member_id ASC makes the choice reproducible. For paged reports, consider whether data can change between pages and whether the selected sort key is stable enough for the reader’s purpose.

Use query validation to verify the result

Compare the final output with an independent check: a hand count of a small sample, a total from the source table, or a second query that isolates the included IDs. Document the period, exclusions, treatment of missing values, and meaning of one row. These details let another analyst understand why the number changed when the next reporting period arrives.

SQL fluency grows from asking precise questions and checking intermediate results. SELECT, JOIN, WHERE, GROUP BY, and ORDER BY provide the mechanics. The analyst supplies the definition of the population, the relationship between tables, and the evidence that the result matches the question.