Subsection 4.3

Specific columns + DISTINCT

~4 min

Reading

SELECT * is useful for a first look. It is not always the best long-term habit.

When you know which columns you need, name them directly:

SELECT voterbase_id, vb_vf_source_state, gender
FROM `prj-training-ao7c.voterfiles.voterfile_snapshot`
LIMIT 1000;

Selecting specific columns makes your result easier to read. It also trains you to think about which fields matter for the question.

You can also use DISTINCT to remove duplicate rows from the selected columns.

Example:

SELECT DISTINCT vb_vf_source_state
FROM `prj-training-ao7c.voterfiles.voterfile_snapshot`;

Use DISTINCT when you want to know the unique values in a column. For example, if a voter file contains millions of rows, the state column may repeat the same state many times. DISTINCT returns each state once.

Key takeaways:

DISTINCT returns unique rows for the columns selected.
It is useful when you want to understand the variety of values in a field.
Place DISTINCT immediately after SELECT.
If you select multiple columns with DISTINCT, BigQuery returns unique combinations of those selected columns.

Learner action

Run one DISTINCT query on a categorical column in your table. What values show up?

SELECT * is fine for a peek, but as soon as you know which columns matter, list them. The result is easier to read and BigQuery scans less data : which becomes important on big tables.

Worked example from the deck

SELECT voterbase_id, vb_vf_source_state, gender
FROM `prj-training-ao7c.voterfiles.voterfile_snapshot`
LIMIT 1000;

DISTINCT : your sanity-check tool

Rosario's framing from the deck:

"When we're seeking to understand the variety of data present in a column, without the clutter of repeated entries : this is where the DISTINCT keyword comes into play."

Find unique values in one column

SELECT DISTINCT vb_vf_source_state
FROM `prj-training-ao7c.voterfiles.voterfile_snapshot`;

Returns one row per unique state : regardless of how many voter records came from that state.

Public-dataset version anyone can run

SELECT DISTINCT state
FROM `bigquery-public-data.usa_names.usa_1910_current`
ORDER BY state;

Returns 51 rows (50 states + DC).

When to reach for DISTINCT:

You inherited a column and want to know what values exist in it.
You suspect duplicates (a "source" field with both "Survey" and "survey").
You want a count of unique categories before committing to a GROUP BY.

Listing specific columns is the rule, not SELECT *. DISTINCT is what you use when you only want unique values : great for asking “what counties show up in this dataset?”

Action: Pick one column on your table to DISTINCT in the next page's activity.

Slide to complete