AI Ecosystem CourseLearn with Q
Data: From Business Records To Model Inputs · Lesson 3

How Queries And Joins Handle Missing Data

A query defines which information reaches an analysis or AI system. Investigate what it keeps, omits, repeats and leaves unknown before interpreting its output.

Study And Save Progress

Watch The Lesson

8 min 26 sec

Read the video transcript

3 orders reached our fictional order-and-returns service. After the orders were connected to customer information, only 2 appeared in the result. Has 1 customer’s problem disappeared, or has our query made it harder to see? Follow the same 3 orders as we select information, connect records, and calculate an average. By the end, you should be able to

explain who remains in a result, what is missing, and which question a number actually answers. A query asks a data system for information. SQL stands for Structured Query Language. It is a language commonly used with relational databases. Suppose we ask for each order’s identifier and amount, keeping only orders whose recorded status is shipped. We specify the fields we want, the source to use,

and a condition for keeping a row. An order recorded as pending falls outside that request. The query has selected a population; it has not established whether the status was recorded accurately. Now take 3 shipped orders: O101, O102, and O103. They refer to customers C7, C8, and C9.

The customer information available for this example contains C7 and C8, 1 time each, with no entry for C9. Each order represents 1 order, and customer identifiers are unique in the customer information. Those assumptions let us see what the connection itself changes. A join combines records under a matching rule. Here the rule compares the customer identifier on

an order with the identifier in the customer information. An inner join returns matching combinations. The 1st order connects to C7, and the 2nd connects to C8. The 3rd finds no match and contributes no row to this result. All 3 source orders still exist. We have obtained 2 connected order

records by excluding the unmatched case. That can be useful when the question is specifically about orders with matching customer information. Our service team has a different purpose: investigate every shipped order, including broken relationships. A left join starts from the orders and retains each of them. The 1st and 2nd gain matching customer information.

The 3rd remains, with missing values in the fields that would have come from the customer side. Its original customer identifier, C9, is still present on the order. We can investigate whether an extract is incomplete, an identifier is wrong, or some other source problem occurred. The join alone cannot settle which explanation is correct.

This is how the PostgreSQL 18 documentation demonstrates joins: its own weather-and-city example loses an unmatched place under an inner join and retains it under a left outer join. Our orders are an invented application of that documented behavior. Choosing the join is therefore part of defining the service’s population. A successful connection says that the

values matched under the rule; checking the underlying business meaning remains a separate responsibility. A retained missing field needs careful interpretation. SQL uses NULL to represent missing or unknown information. In our unmatched order, the customer-side name is unavailable in the joined result. That does not tell us that the customer has no name.

An unknown return reason likewise differs from a confirmed statement that no return occurred. Keep the reason and the occurrence of a return as separate questions. A blank-looking result can conceal several different information states. The same issue changes arithmetic. Consider a separate, fictional set of 3 return cases, with all amounts in the same currency and period.

2 handling costs are known: $10 and $20. The 3rd cost is missing. The known costs total $30. Their average is 30 divided by 2, or $15 per case with a known cost. We should report that 2 of the 3 cases supplied a cost.

The average across all 3 cases remains unknown. Now suppose someone replaces the missing cost with 0. The displayed total remains 30, but the divisor becomes 3 and the average becomes $10. That change came from a replacement rule. It supplied a value that the evidence did not contain. A confirmed 0 cost would justify including 0; a missing cost requires investigation or an explicitly justified treatment.

PostgreSQL’s average function uses non-null input values, so its result also needs the coverage information beside it. Connections can increase a result as well as shrink it. Recall the $120 order with 3 product lines. Matching that order to its 3 lines produces 3 combinations, each carrying the same order-level total. Adding those repeated totals gives $360. The order’s value has stayed the same.

The result now has a finer grain. Calculate at the intended grain, or aggregate the lines appropriately, before combining totals. Check which identifiers survive, which repeat, and which fields become missing at each step. These choices also affect what a model appears to handle well. Suppose an evaluation keeps only cases with matching customer information.

Cases with unresolved relationships can disappear before the model is tested. A score on that retained group answers a narrower question than a score covering the service’s full workload. It could look better if the excluded cases were harder, but the join alone tells us nothing about the size or direction of a score change. Record the exclusions and test the relevant unresolved cases deliberately.

Missing cases also matter outside a model test. Public Health England reported on October 4, 2020 that a data-load issue had kept 15,841 positive cases out of the figures expected between September 25 and October 2. People had received their test results normally. The reporting population was incomplete even though the source events existed.

The statement identifies a transfer issue, rather than a SQL join. It shows why reconciling source cases with the reported population matters before interpreting a result. Here is the checkpoint. Why can a model appear to perform better after an inner join? Think about which cases reach the evaluation.

Difficult or incomplete cases may have been removed when they failed to match. The evaluation may then describe the retained subset rather than the real operating population. Compare the included and excluded cases before interpreting a performance claim. Try a 2nd check. 3 return cases have costs of $10,

$20, and an unknown amount. What does the $15 average describe, and what would change if the unknown became a confirmed 0? $15 describes the 2 cases with known costs. A confirmed 0 would make all 3 costs known, giving a $10 average across the 3 cases. Replacing an unknown with 0 without evidence would impose that same calculation through an assumption.

For practice, investigate a new set of orders with 1 missing customer match and 1 order connected to several return events. Explain which orders an inner join would retain, what a left join would expose, and where an order total could repeat. Then calculate a known-value average and state its coverage. The worksheet gives the cases, with a

separate answer key for checking your reasoning. Our 3rd order never stopped mattering to the service. The query changed whether we could see it, the join changed which combinations appeared, and the treatment of a missing amount changed the denominator. Before using a result as an AI input or evaluation set, explain its selection rule, grain, missing information, and coverage.

Next we will examine the systems that hold operational facts and analytical copies, and why their different purposes matter.

Main Point

A query defines which information reaches an analysis or AI system. Investigate what it keeps, omits, repeats and leaves unknown before interpreting its output.

Key Points

1. Start from the service’s question

The continuing case is a fictional company’s order-and-returns assistant. A service investigator needs to see every shipped order, including records whose customer relationship needs attention. Begin with the fields and condition required for that purpose. The familiar expression below means: return order identifiers and amounts from the orders source for rows whose recorded status equals shipped. SQL is Structured Query Language. [PG-SELECT]

```sql

SELECT order_id, amount

FROM orders

WHERE status = 'shipped';

```

The request relies on the recorded status. It cannot establish that the source captured shipment correctly. Also, these snippets teach selection and matching; they are not a production access-control or query-security design.

2. Follow the missing match

Use exactly these invented records. Each order identifier and each customer identifier is unique in its own source. All three orders are shipped.

Joining on customer_id with an inner join yields O101–C7 and O102–C8. O103 contributes no row because it has no matching customer entry. No source order is deleted.

```sql

SELECT o.order_id, o.customer_id, c.customer_name

FROM orders AS o

INNER JOIN customers AS c ON o.customer_id = c.customer_id;

```

A left join starting from orders returns all three orders. For O103, the customer-side name is NULL. The order-side customer_id still contains C9. That distinction helps locate the broken relationship rather than erasing evidence of it. [PG-JOIN]

```sql

SELECT o.order_id, o.customer_id, c.customer_name

FROM orders AS o

LEFT JOIN customers AS c ON o.customer_id = c.customer_id;

```

With the stated unique customer keys, these results have two and three rows respectively. In a general left join, several matching right-side records can produce several output combinations for one left-side record. “Retains each left record” therefore does not guarantee the original row count. [PG-TABLE]

For a question about matched customers, the inner result can be appropriate. For investigating all shipped orders, losing O103 conceals relevant work. Check the source extract and identifier assignment; the cause of the missing relationship remains unknown.

The PostgreSQL 18 tutorial provides a real documented demonstration using weather and city records, including an unmatched place. It verifies the database behavior described here; it provides no measured evidence about this invented company’s outcomes. Version 18 documentation was checked on 29 October 2026. [PG-JOIN]

3. Interpret NULL and the denominator

NULL can mark unavailable or unknown information. Testing for it uses `IS NULL`; ordinary equality with NULL does not behave like equality between known values. A missing customer name cannot establish that a person has no name. An unknown reason for a recorded return cannot establish that no return happened. [PG-NULL]

For a separate invented set of three return cases, all costs use the same currency and period: $10, $20 and unknown. The known-value calculation is:

($10 + $20) ÷ 2 = $15 per case with a known cost; coverage = 2 of 3 cases.

The complete three-case average is unknown. Replacing the missing value with zero gives ($10 + $20 + $0) ÷ 3 = $10 per case, imposing a value. A confirmed zero would support that calculation. PostgreSQL `AVG(cost)` averages non-null values; `COUNT(*)` and `COUNT(cost)` can reveal total records and records contributing a cost. [PG-AGG]

```sql

SELECT COUNT(*) AS cases,

COUNT(cost) AS known_costs,

AVG(cost) AS known_cost_average

FROM return_cases;

```

This calculation assumes one row per return case. A join that repeats cases would require the grain to be repaired first.

4. Reconcile a changing result

One $120 order matched to three order lines produces three combinations carrying that order total. Summing it at line grain produces $360. A join can therefore multiply representations while leaving the underlying transaction unchanged. [PG-TABLE]

Ask four questions after each transformation:

  1. Which source identifiers were included or excluded, and why?
  2. How often does each identifier now occur?
  3. Which fields became missing, and what do those states mean?
  4. Which denominator and units support the reported calculation?

Keep a record of these answers alongside the query definition. Evaluation on only matched cases can omit unresolved cases that the service must handle. Whether its measured score rises or falls depends on the excluded cases; no score is supplied in this lesson. Population coverage is a prerequisite for interpreting a score.

5. Practice and transfer

Use the separate worksheet before opening the answer key. Choose the result that serves the service question, identify a potential multiplication, and report both a known-value average and its coverage. The next lesson follows those results into operational and analytical systems.

6. A dated example

Missing cases also matter outside a model test. Public Health England reported on October 4, 2020 that a data-load issue had kept 15,841 positive cases out of the figures expected between September 25 and October 2. People had received their test results normally. The reporting population was incomplete even though the source events existed. The statement identifies a transfer issue, rather than a SQL join. It shows why reconciling source cases with the reported population matters before interpreting a result.

7. Bringing the investigation together

The service question requires every shipped order to remain inspectable, including O103 with its unresolved customer link. Choose the join for that purpose, reconcile repeated order amounts, and report the known-cost average alongside its coverage. These checks explain which source evidence reached the result and what still needs investigation.

Learning Checks

Attempt each answer before expanding the reasoning.

M13-L03-Q01

Why can a model appear to perform better after an inner join? Think about which cases reach the evaluation.

Show reasoning

Difficult or incomplete cases may have been removed when they failed to match. The evaluation may then describe the retained subset rather than the real operating population. Compare the included and excluded cases before interpreting a performance claim.

M13-L03-Q02

Three return cases have costs of ten dollars, twenty dollars, and an unknown amount. What does the fifteen-dollar average describe, and what would change if the unknown became a confirmed zero?

Show reasoning

Fifteen dollars describes the two cases with known costs. A confirmed zero would make all three costs known, giving a ten-dollar average across the three cases. Replacing an unknown with zero without evidence would impose that same calculation through an assumption.

Sources

Learner Materials

Download Lesson 3 Learner Materials

Use the supplied fictional cases and templates for practice.

Practice

Write your answer, compare it with the model answer and decide what to revisit. These written checkpoints are self-assessed.

Here is the checkpoint. Why can a model appear to perform better after an inner join? Think about which cases reach the evaluation.

Show The Model Answer

Difficult or incomplete cases may have been removed when they failed to match. The evaluation may then describe the retained subset rather than the real operating population. Compare the included and excluded cases before interpreting a performance claim.

Compare your reasoning with the model answer. These written checks are self-assessed.

Try a second check. Three return cases have costs of ten dollars, twenty dollars, and an unknown amount. What does the fifteen-dollar average describe, and what would change if the unknown became a confirmed zero?

Show The Model Answer

Fifteen dollars describes the two cases with known costs. A confirmed zero would make all three costs known, giving a ten-dollar average across the three cases. Replacing an unknown with zero without evidence would impose that same calculation through an assumption.

Compare your reasoning with the model answer. These written checks are self-assessed.

Open Practice And Save Your Work