UNION ALL, UNION and INTERSECT

Set operators combine compatible query outputs. The worked example preserves occurrences from two channels: all Ukrainian role identities and all active role identities. Identities 1 and 2 belong to both channels and therefore appear twice under UNION ALL.

The contract is an occurrence list, not a deduplicated catalogue. Its ordered output is 1, 1, 2, 2, 3, 4, 6, 7, 8. If the consumer instead asks for a unique eligible-role set, UNION would express a different and equally deliberate requirement.

UNION ALL concatenates result occurrences. UNION removes repeated complete tuples, while INTERSECT keeps distinct tuples present in both inputs under the default forms used here. Both input projections must have compatible column counts and types. Order belongs on the combined result unless a nested input has its own justified slice.

The one-column identity projection makes membership easy to inspect. Adding a channel label would change tuple equality and prevent two appearances of the same identity from collapsing under UNION. Always reason about the complete projected tuple rather than an informal entity description.

The counterexample uses UNION and loses the duplicate occurrences for identities 1 and 2. That is wrong for this occurrence contract but appropriate for a distinct catalogue. A second common error assumes UNION ALL automatically performs business deduplication across feeds.

Feed reconciliation needs a canonical identity and precedence policy beyond a set operator. Different source keys or normalized fields can represent one business entity, while equal titles can represent separate legitimate roles. Do not confuse tuple equality with entity-resolution evidence.

Return the distinct identities that are both Ukrainian and active using INTERSECT. Predict 1 and 2. Keep each input query simple and let the set operator express shared membership rather than carrying over the occurrence-list requirement from the worked example.

This is a small change in the combination rule, not a new join problem. Verify that archived Ukrainian role 7 is excluded and active non-Ukrainian role 4 is excluded. Those negative cases prove both sides of the intersection.

Step 1: Write the Ukrainian input. Step 2: Intersect it with the active input.

The solution returns identities 1 and 2 exactly once. It does not retain a channel count or choose a representative source record. If those additional facts are needed, preserve provenance in a separate projected field or aggregate it with an explicit entity key.

For performance discussion, note that deduplication requires work but avoid declaring UNION ALL universally correct merely because it can be cheaper. Semantics choose the operator first; then inspect representative execution plans and input volumes.

Interview tasks often use combined audiences, missing membership or overlapping event streams. State whether the result represents a bag of occurrences or a set of distinct tuples. Explain a single overlapping identity and predict how many times it should appear.

The next lesson uses named intermediate results to make a multi-stage population easier to review. Names improve readability and grain reasoning; they do not by themselves guarantee that an optimizer materializes an intermediate table.

Optional stretch — after the core task works: Preserve channel provenance and count appearances per role without confusing them with unique-role counts.

Computer practice: https://applydjinn.com/typorithm/sql-lab-20261105.zip. Load the shared setup.sql fixture before running a snippet. The local verifier compares exact ordered row arrays in SQLite and PostgreSQL, keeps SQL values separate from driver bindings and never connects to the production website database.