{"schema_version":"2.0","record_type":"article","canonical_url":"https://marketingwiki.ai/articles/sql-email-audience-join-fanout","id":"sql-email-audience-join-fanout","slug":"sql-email-audience-join-fanout","title":"Count People, Not Joined Rows, in SQL Email Audiences","description":"Use a runnable synthetic SQL example to expose join multiplication before a research query becomes a campaign audience or revenue claim.","dek":"A small cohort experiment distinguishes row counts, unique identities, and eligible recipients before Migma creative is prepared.","category":"Email Operations","topics":["Migma","email marketing","Runnable SQL fixture with cardinality assertions"],"publishedAt":"2026-09-10","updatedAt":"2026-09-14","lastVerifiedAt":"2026-09-14","readingMinutes":5,"author":"Marketing Wiki Research Automation","reviewer":null,"featured":false,"sources":[{"title":"Migma: Persona","url":"https://docs.migma.ai/creating-emails/persona?utm_source=marketingwiki&utm_medium=referral&utm_campaign=sql-email-audience-join-fanout"},{"title":"Migma: Lists and Segments","url":"https://docs.migma.ai/audience/manage-tags-and-segments?utm_source=marketingwiki&utm_medium=referral&utm_campaign=sql-email-audience-join-fanout"},{"title":"SQLite: SELECT","url":"https://www.sqlite.org/lang_select.html?utm_source=marketingwiki&utm_medium=referral&utm_campaign=sql-email-audience-join-fanout"},{"title":"SQLite: Expressions","url":"https://www.sqlite.org/lang_expr.html?utm_source=marketingwiki&utm_medium=referral&utm_campaign=sql-email-audience-join-fanout"}],"wordCount":810,"body":"Before using a SQL result to brief a Migma campaign, prove that the query counts the entity you intend to email. A join can return several rows for one person. A plausible total can therefore represent purchases, event combinations, or duplicated profiles rather than recipients.\n\n> **Editorial disclosure:** Prepared by Marketing Wiki Research Automation under standing direct-publication authorization and not independently reviewed. Product capabilities are vendor-documented unless labeled otherwise; sources were refreshed on September 10, 2026.\n\nMigma’s [persona workflow](https://docs.migma.ai/creating-emails/persona?utm_source=marketingwiki&utm_medium=referral&utm_campaign=sql-email-audience-join-fanout) can shape a draft for an audience, while [lists and segments](https://docs.migma.ai/audience/manage-tags-and-segments?utm_source=marketingwiki&utm_medium=referral&utm_campaign=sql-email-audience-join-fanout) organize recipients. Neither step repairs a wrong upstream cohort definition. Keep the data query, creative brief, and final sending audience as three separately checked artifacts.\n\n## A six-row audience that contains two people\n\nA fictional ceramics studio wants to invite previous buyers to a glaze-care workshop. Its analyst joins customers, orders, and page visits. One customer has two orders and two visits; another has one order and two visits.\n\nThis fixture runs in SQLite:\n\n```sql\nCREATE TABLE people (person_id TEXT PRIMARY KEY);\nCREATE TABLE orders (order_id TEXT, person_id TEXT);\nCREATE TABLE visits (visit_id TEXT, person_id TEXT);\nINSERT INTO people VALUES ('A'), ('B'), ('C');\nINSERT INTO orders VALUES ('o1','A'), ('o2','A'), ('o3','B');\nINSERT INTO visits VALUES\n  ('v1','A'), ('v2','A'), ('v3','B'), ('v4','B');\n\nSELECT p.person_id, o.order_id, v.visit_id\nFROM people p\nJOIN orders o ON o.person_id = p.person_id\nJOIN visits v ON v.person_id = p.person_id;\n```\n\nThe result has six rows: four combinations for A and two for B. There are only two represented people. This follows the matching-row behavior documented in [SQLite’s SELECT reference](https://www.sqlite.org/lang_select.html?utm_source=marketingwiki&utm_medium=referral&utm_campaign=sql-email-audience-join-fanout). It is not evidence of six customers, six purchases, or six qualified email addresses.\n\nA `COUNT(DISTINCT person_id)` can repair this particular person count. It does not automatically repair every aggregate in the same query. If an order amount appears on each joined row, summing it still repeats the amount for each matching visit. Fix the grain of the input before calculating money or activity totals.\n\n## State the business predicate before repairing the SQL\n\nFor the workshop invitation, suppose the actual rule is “a person has at least one order and at least one recorded visit.” The query needs existence, not every order-visit combination:\n\n```sql\nSELECT p.person_id\nFROM people p\nWHERE EXISTS (\n  SELECT 1 FROM orders o\n  WHERE o.person_id = p.person_id\n)\nAND EXISTS (\n  SELECT 1 FROM visits v\n  WHERE v.person_id = p.person_id\n);\n```\n\nThe expected result is A and B, once each. [SQLite documents `EXISTS`](https://www.sqlite.org/lang_expr.html?utm_source=marketingwiki&utm_medium=referral&utm_campaign=sql-email-audience-join-fanout) as a test of whether the subquery returns any row. The choice of existence is our proposed implementation of this specific business rule. A different rule, such as “visited after the most recent purchase,” needs ordering and time conditions that this fixture deliberately does not provide.\n\nWrite those omitted conditions down. Otherwise a clean two-row result can conceal a semantically wrong audience just as easily as a six-row result.\n\n## Make cardinality a reviewable artifact\n\nRetain these outputs beside the query:\n\n| Check | Result in this fixture | What it establishes |\n| --- | --- | --- |\n| Rows in the naive join | 6 | Order-visit combinations |\n| Distinct people in the join | 2 | Represented identities |\n| Rows from the existence query | 2 | One row per matching person |\n| People with no matching order | C | A negative example is excluded |\n\nFor real data, add the source snapshot time, identity key, date window, timezone, handling of deleted records, and the intended row grain. Inspect a few positive and negative identities using approved internal access. Do not export personal data into an unnecessary prompt just to explain a count.\n\nA useful assertion is `row_count = distinct_person_count` when the contract promises one row per person. That assertion catches duplicate identity rows. It does not establish consent, deliverability, correct date logic, or whether two person IDs belong to one human.\n\n## Move from evidence to Migma creative\n\nGive the Migma brief the verified audience description: previous ceramics buyers eligible for a care workshop invitation. Include the actual workshop facts and avoid telling the generator that all qualifying people viewed a particular product unless the query established that condition.\n\nUse a synthetic persona when testing the draft. Then review the actual list or segment used for sending. Record three counts with different labels: analytical cohort, identities successfully mapped into the sending system, and currently eligible recipients. Investigate differences; do not force them to match by weakening exclusions.\n\nThe studio can now say why A and B qualify and why C does not. That is more useful than a large audience number. The fixture demonstrates join multiplication locally; it makes no claim about a vendor’s warehouse schema, SQL preview, integration availability, or production query performance.\n\nFor adjacent controls, use [natural-language segmentation QA](/articles/natural-language-email-segmentation-qa) for filter meaning and the [audience union check](/articles/multi-segment-email-audience-union-check) for overlapping selected groups."}