DISTINCT, duplicates and business identity

DISTINCT removes repeated complete output tuples. The worked query asks which countries occur among active roles, so returning each country once is appropriate. There are four country values: CA, IL, UA and US. The query does not count or remove role records.

Deduplicating values differs from merging equivalent business entities. Roles 2 and 7 share a title but have different identities and statuses. Neither title equality nor the presence of a repeated country proves that the underlying role records should be merged.

The chosen select list determines the tuple considered distinct. SELECT DISTINCT country collapses repeated countries, while adding id would keep every role identity distinct. Apply the active population rule before reasoning about the resulting set of countries.

ORDER BY country gives the expected display order. A set membership test can ignore order if the consumer contract does not require it, but the query here declares one deliberately. Counting distinct values is a separate aggregate operation introduced later.

The counterexample returns country without DISTINCT, repeating countries with several active roles. It answers a list-per-role question rather than a country vocabulary question. Conversely, adding DISTINCT after an incorrect join can conceal multiplied rows without repairing the join relationship.

Before removing repetitions, identify why they exist. A one-to-many skill join legitimately creates one row per role-skill pair. If the consumer needs roles with a skill, an existence test or an appropriate aggregation may be clearer than a broad DISTINCT patch.

List distinct titles from the complete role table, including archived records. Predict seven titles because the two QA Engineer records share one title value. This is a vocabulary task, so it intentionally discards identity while retaining each unique output label.

Choose only title in the DISTINCT projection, then order it. Do not include id, active or publication date; those additional columns would make the two QA tuples different again. Explain why this exercise is not an instruction to delete either source record.

Step 1: Select only the title column. Step 2: Request unique output values and sort them.

The solution returns seven alphabetically ordered labels. It does not modify roles and does not choose a representative record for a repeated title. The absence of the second QA label is a property of the output projection, not an archival or deduplication operation.

A good acceptance check compares the actual title set and verifies that both QA role identities still exist in the source. In this lab, every example starts from the same disposable fixture so one lesson cannot silently change another lesson's expected population.

Interviewers may ask whether DISTINCT is slow or whether it fixes duplicates. Explain that its semantic job is tuple deduplication; cost depends on input size, sorting or hashing, indexes and the surrounding plan. Diagnose the grain before optimizing the operator.

When business deduplication really is required, define a stable canonical key and a precedence rule for competing records. A later analytics capstone uses a deliberate ranking rule. The next lesson computes labels without collapsing the identities behind them.

Optional stretch — after the core task works: Show how SELECT DISTINCT country,id differs from SELECT DISTINCT country.

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.