Review AI-Assisted SQL Joins Before Trusting Row Counts

An SQL join can return more rows than the list you started with without adding any new task to that list. One task may match several events, while another may have no event at all. Before trusting an AI-assisted query's total, identify what one output row represents and which records the query preserves. A successful query execution establishes that the database accepted the expression; it does not establish that the expression answers the intended business question.

This guide uses four fictional tasks and four fictional event records to examine inner joins, left joins, filter placement and counts. The proposed queries are deliberately authored, not observed model outputs. Their manually specified expectations are checked in a local, in-memory SQLite exercise, not against a client or production database. No live integration, data access permission, security certification or operating approval is supplied. The purpose is to make a small join inspectable before a similar-looking query is trusted with a real reporting task.

State what the report is supposed to count

The task table hasT1 andT2 assigned to teamA, andT3 andT4 assigned to teamB. Each task identifier appears once. The event table hasE1 forT1 with kinddone, E2 forT1 with kindnote, E3 forT2 with kinddone andE4 forT9 with kinddone. T9 is not in the task table. These labels are invented. An event called done is only a field value in the exercise; it is not evidence that an actual task was completed or an action was authorized.

One possible question is how many task records are in the supplied task list. The answer is four before any join. Another is how many event records match those tasks. Under the stated identifiers, E1,E2 andE3 match, so that answer is three. A third question is how many tasks have at least one matching event. T1 andT2 qualify, so that answer is two. These counts differ because they describe different populations, not because one of them is automatically a bad calculation.

Write the intended question before reading the proposal. If the report should retain every listed task, a query that returns only tasks with events may be incomplete. If the report should count events, counting the task identifiers after a one-to-many join can overstate the number of distinct tasks. The query's row count should not quietly redefine the reporting objective. Keep the source population, matching rule and intended unit of output separate in the review.

The source identifiers also need a definition. Here task_id links an event to a task by exact equality, and the exercise establishes unique task identifiers. A real dataset may use a different key, contain duplicate identifiers or require a compound relationship. Do not borrow the teaching assumptions without checking them. A join can be syntactically correct while using an inappropriate key, and a plausible row count cannot repair that mismatch by itself.

Read a joined row as a matching pair

The first teaching query uses an inner join between tasks and events on matching task identifiers. Its ordered result has three rows: T1 withE1, T1 withE2 andT2 withE3. T1 appears twice because it matches two event records. That is the declared relationship in the inputs, not an accidental duplicate that should automatically be deleted. The output row represents a matching task-event pair, while the source task table represents one record per task.

T3 andT4 do not appear in that inner-join result because the supplied event table has no matching identifier for them. E4 also does not appear because its task referenceT9 has no matching task record. This particular query therefore does not retain every input row from either table. Its three output rows are not a complete inventory of tasks or events. State the preserved population rather than calling the result the combined data without qualification.

The SQLite SELECT documentation provides the primary language context for the joins used here. It distinguishes matching input rows and the behavior of outer joins. The original teaching expectations come from the explicit small input relationships and are checked separately by local execution. The article does not claim that every SQL dialect, database configuration or query feature behaves identically, and it does not supply a universal parser or validator for arbitrary SQL.

If the requested report needs one row per task, do not assume that an inner join automatically provides that shape. The one-to-many relationship must be handled under a declared rule, such as an appropriate aggregation or another justified design. This guide does not prescribe which event should be selected for a real task. Choosing the latest, first or preferred event would introduce a separate rule requiring its own definition and evidence.

Understand what a left join preserves

The second teaching query starts from tasks and uses a left join to events on task_id. Its ordered result has five rows. T1 still matchesE1 andE2; T2 matchesE3; T3 andT4 each receive an unmatched row whose event fields are SQLNULL. The join retains all four source tasks, but it does not promise exactly one row per task. Preservation of the left population and uniqueness of the output rows are different properties.

The unmatched rows matter to the report's meaning. A NULL event_id in this result means no matching event row was supplied under the chosen join condition. It does not mean that a real task has no activity anywhere, that the task failed or that a process was never performed. The exercise contains only the two fictional tables and the stated equality rule. Keep that source boundary with the result instead of converting an unmatched join into a broader operational finding.

E4 remains outside this left-join result because tasks is the preserved side and no supplied task has identifierT9. A left join from tasks is not a complete inventory of the event table. If a reader needs to investigate unmatched event references, that is a separate query and a separate population. The fixture includesE4 deliberately so the difference is visible. Do not claim that a left join preserves all data merely because the word left sounds inclusive.

NULL is also distinct from an empty string or the text0. The expected JSON uses null to represent the unmatched SQL values in the local exercise. It does not replace them with blank text or zero counts. If a real report chooses a display label for missing matches, keep that presentation rule separate from the underlying SQL value. Otherwise a formatting choice can make the report appear to contain a supplied value where the join actually found no match.

Check whether the filter belongs to matching or final selection

Suppose the question is to retain all supplied tasks but attach only events whose kind isdone. One authored query places the kind condition in the ON clause of the left join. Under the fixture, T1 matchesE1, T2 matchesE3, andT3 andT4 retain unmatched rows. The result has four rows and two non-NULL event identifiers. E2 is not an attached event because its kind is note, while the taskT1 remains represented throughE1.

A different authored query performs the broader left join and then applies WHERE e.kind='done'. The unmatched rows do not satisfy that condition in this exercise, so the result contains onlyT1 withE1 andT2 withE3. It has two rows and omitsT3 andT4. Both expressions can execute, but they answer different questions. The placement of the condition changes which output population survives the query.

This is a precise review question for an AI-assisted proposal. Ask whether the intended rule is to restrict which event rows may match while retaining all tasks, or to select only final rows that satisfy the event condition. Do not accept a formula-like explanation saying both filter to done without examining the preservation effect. A shared condition text does not make its placement irrelevant, and a correct-looking result on tasks that all have events can hide the difference.

The fixture deliberately includes unmatched tasks so that the two proposals can be distinguished. A test set containing onlyT1 andT2 would not expose the same preservation issue. That does not mean this tiny fixture is representative of a real operating population. It means the authored examples were chosen to make one specific semantic difference inspectable. Use the separate evaluation-dataset guide for the broader question of selecting and protecting actual evaluation cases.

Name what each count function counts

On the unfiltered left-join result, COUNT(*) is five because it counts output rows. COUNT(e.event_id) is three because the event identifier is non-NULL on the three matched rows. COUNT(DISTINCT t.task_id) is four because the preserved task identifiers areT1,T2,T3 andT4. These are three valid expressions over the same result, with different meanings. The intended report should determine which meaning is relevant rather than choosing whichever number looks most plausible.

SQLite's aggregate-function documentation distinguishes counting rows from counting non-NULL values. That limited definition supplies context for the exercise. A count of event identifiers also depends on the input and query assumptions: here each actual event_id is supplied and unique. If a different dataset permits missing identifiers, counting that column would answer a narrower question. Do not promote a function name into a business definition without checking the field and the population it is applied to.

Grouping by team reveals the same distinction. The task-preserving left join produces three rows for teamA and two for teamB. TeamA has two distinct tasks and three matched events; teamB has two distinct tasks and zero matched events. Reporting3 and2 as the number of tasks would confuse joined rows with unique task records. Reporting3 and0 as the number of all source events would also omitE4, whose reference does not match a supplied task.

Keep group labels and inclusion rules visible in any summary. A row assigned to teamA in the task table does not establish who performed each event or whether a team is more productive. The fixture supplies no effort, quality, date or causal information. It supports cardinality comparisons under a stated relationship, not a performance ranking. An AI-generated narrative should not turn a count of matching records into an unsupported judgment about people or operations.

Do not use DISTINCT to hide an undefined relationship

DISTINCT can be appropriate when the requested output is a set of unique values, but it should follow a declared purpose. Adding it merely to make a total look closer to the source task count can hide the fact that the join represents multiple event relationships. If the report needs event-level information, collapsing rows may discard distinctions the reader actually needs. State the output unit first, then evaluate whether deduplication matches that unit.

For example, selecting distinct task_id from the inner join producesT1 andT2 in the fixture. That answers which supplied tasks have at least one matching event. It does not restoreT3 andT4, which were omitted by the inner join, and it does not produce an all-task inventory. A reader who expects four tasks cannot repair the population mismatch simply by removing repeated task identifiers from the three-row inner-join result.

Likewise, selecting a task name and event kind can collapse rows differently from selecting task_id and event_id. The columns chosen affect what the database considers a duplicate result row. This guide supplies no rule that labels are unique or that two events of the same kind are interchangeable. In a real task, identify stable keys and the intended relationship. A display label should not silently replace record identity because it makes the output easier to read.

If the relationship is unclear, a useful next step is an explicit question about the data model. Are multiple events per task expected? Can events refer to tasks outside the supplied extract? Which side defines the reporting population? Those questions can be answered without inventing a favorable aggregate. An unresolved model question is more informative than a deduplicated table whose meaning no one can explain.

Compare independently expected rows before the headline total

The downloadable fixture includes the two original CSV tables and a JSON file containing authored SELECT queries with manually specified expected rows. The local verification creates only an in-memory SQLite database, loads the fictional values and compares ordered query results with those expectations. It makes no network call or production database connection. This establishes a small, reproducible comparison under the recorded local runtime, not a claim that an arbitrary AI-generated query is safe or correct.

Ordering is explicit in the teaching queries so row-by-row comparisons are understandable. An unordered result should not be treated as a stable sequence merely because one local run happened to return a familiar order. If the actual task does not care about order, define an appropriate comparison method rather than relying on incidental presentation. The exercise uses ordering to simplify inspection, not to imply that every report must use the same sort keys.

A matching aggregate is useful but can conceal a wrong population or offsetting differences. Inspect which task-event pairs appear, which task identifiers are repeated and which sides have unmatched records. A count of five can arise from more than one relationship in a different dataset. The small expected row lists make this exercise's interpretation explicit. They do not replace examining the schema, keys, filters and task definition in the actual system.

If an assistant says it tested a query, ask what was executed and where the expected answer came from. A generated explanation, a plausible table or an execution without an independent expectation is not the same comparison. This article does not report any model response. The proposals are authored teaching expressions, and the local results are identified as SQLite fixture checks. Keep those evidence types separate when describing the work to an operator.

Keep the result inside its actual authority and scope

Reading a join correctly does not establish access permission for the underlying data. The public fixture contains no private information and no credentials. A real workflow may require authorization for the source records, the database connection and any later action based on the report. This article supplies none of those permissions. It explains a query-reading task that can be discussed without connecting to an actual operating system or inventing client evidence.

The related CSV handoff guide addresses whether text survives serialization and application interpretation. The table-meaning guide addresses whether a structured representation preserves the source's claims and qualifiers. This join guide addresses a different boundary: how a declared relationship changes row populations and counts. Keeping those questions separate makes a review more precise. A broad statement that the data was checked should not stand in for identifying which boundary was actually examined.

A useful review note names the source population, key relationship, join type, filter placement, output unit and independent comparison. It also states what remains unresolved. If the query correctly counts matching events but the business wanted all tasks, say that directly. Do not rewrite the business question after seeing the output or declare the system reliable because one count agrees with a remembered number.

The practical goal is a query that can be explained in terms of the intended records and the stated rule. That explanation should survive simple boundary cases such as multiple matches, no match and an unmatched reference on the other side. The original fixture supplies those cases openly. It is a teaching aid for a bounded review, not a complete database test suite, a model benchmark, a security assessment or approval to use the query in production.

The unmatched eventE4 is a useful reminder that a task-centered report and an event-centered report can disagree without either calculation being defective. The complete event table has four records, while only three match the supplied tasks. If someone expects those totals to agree, ask whether the task extract is supposed to cover every event reference. The fixture deliberately does not impose that requirement, so the mismatch is visible without becoming a data-integrity finding about an actual system.

Similarly, the repeatedT1 identifier in the joined result is expected under the declared two-event relationship. It is different from finding duplicateT1 source records where a task key was supposed to be unique. Those situations can require different investigation and treatment. A cleanup that removes repeated identifiers from every intermediate result may erase legitimate relationships. Identify which table or output is supposed to be unique before interpreting repetition as an error, and keep the rule with the comparison.

Frequently asked questions

Why can four tasks produce five left-join rows?

T1 matches two event records, T2 matches one, andT3 andT4 each retain an unmatched row. The result preserves four distinct tasks while representing five output rows. A left join does not promise one row per left-side identifier when several right-side records can match.

Does an inner join retain every task?

Not in this fixture. It returns the three matching pairs involvingT1 andT2 and omitsT3 andT4. That may be suitable for a question about tasks with events, but it does not answer an all-task inventory question. The intended population must be stated before judging the query.

Are ON and WHERE filters interchangeable in a left join?

Not generally for the preservation question shown here. Filtering event kind in ON retains unmatched tasks, while the authored WHERE condition removes the unmatched rows. Inspect the intended match rule and final selection separately rather than assuming that identical condition text produces identical populations.

Why do COUNT(*) and COUNT(event_id) differ?

COUNT(*) counts the output rows, including unmatched rows. In the fixture, event_id is NULL on the two unmatched rows, so counting that column gives three rather than five. The relevant business measure still requires its own definition and input assumptions.

Were the example queries generated by an AI model?

No. They are deliberately authored SELECT proposals with manually written expected results. Local in-memory SQLite execution checks those fictional expectations. No model output, client database, live integration, professional review or measured model accuracy is represented.

Does matching the fixture authorize production use?

No. It establishes a limited local comparison on the supplied inputs and recorded runtime. An actual use needs its own schema, data, requirements, authorization and appropriate review. The downloads are teaching data and query text, not a universal validator or production deployment instruction.