Selection Techniques

📚 Schema reference · towns & counties 3 tables

fips

fips
name
state

pnw_counties

county_id
county
state
fips_short
county_seat
county_seat_town_id
year_established
origin
etymology
population_2022
land_area_sq_mi

pnw_towns

town_id
town
state
primary_county
secondary_county
tertiary_county
primary_county_id
secondary_county_id
tertiary_county_id
population_2020_census
population_2010_census
land_area_sq_mi

Selecting columns/fields

Imagine you work for a regional planning agency and need to pull specific information from your database. The SELECT statement lets you choose exactly which columns you need.

SELECT decides which columns come back, and it never changes how many rows do. Scroll through one small table to see the difference, and what AS does to the result.

SELECT chooses columns, never rows

SELECT town, pop
  FROM towns;

Scroll to build the diagram, one step at a time.

Step 1 · FROM names the table FROM towns puts the whole table on the desk: every column and every row. Nothing has been chosen yet.

Step 2 · SELECT lists columns SELECT town, pop names the columns to keep. state and county fade out. Look at the rows: all four are untouched.

Step 3 · the result The result is narrower, not shorter: 2 columns, still 4 rows, in the order you listed them. SELECT never removes rows. That is the job of WHERE.

Step 4 · SELECT * * means every column, in table order. It is handy for a first look at a table. In a query you keep, name the columns so the result does not change when the table does.

Step 5 · AS renames the output AS gives a column a new name in the result only. The values are identical and the table itself still says town and pop.

Text version of this diagram

Step 1 · FROM names the table. FROM towns puts the whole table on the desk: every column and every row. Nothing has been chosen yet.
Step 2 · SELECT lists columns. SELECT town, pop names the columns to keep. state and county fade out. Look at the rows: all four are untouched.
Step 3 · the result. The result is narrower, not shorter: 2 columns, still 4 rows, in the order you listed them. SELECT never removes rows. That is the job of WHERE.
Step 4 · SELECT *. * means every column, in table order. It is handy for a first look at a table. In a query you keep, name the columns so the result does not change when the table does.
Step 5 · AS renames the output. AS gives a column a new name in the result only. The values are identical and the table itself still says town and pop.

Selection 1: Create a quick reference list of all town names

Scenario: You need a simple list of town names for a mail merge or dropdown menu.

Selection 2: List all counties in the Pacific Northwest

Scenario: You’re preparing a report header that needs to list every county in the region.

Selection 3: Export all town data for a comprehensive audit

Scenario: An auditor needs the complete towns dataset. Using * selects every column.

Selection 4: Pull county names and populations for a funding report

Scenario: Federal funding is allocated by population. You need county names paired with their 2022 population figures.

Aliasing

Aliases make your output more readable and your queries easier to write. They’re especially useful when column names are long or when you’re doing calculations.

Aliasing 1: Create a clean report with user-friendly column headers

Scenario: You’re sharing data with non-technical stakeholders who won’t understand land_area_sq_mi. Show each county’s county as county_name, its population_2022 as population, and its land_area_sq_mi as area_square_miles.

Aliasing 2: Use table aliases to simplify longer queries

Scenario: When writing complex queries with multiple tables, short table aliases save typing and improve readability. Select the county, county_seat, and year_established columns from the counties table.

Unique entries

DISTINCT removes duplicate values, helping you understand the unique categories in your data.

DISTINCT: repeated values collapse to one

SELECT DISTINCT state
  FROM towns;

Scroll to build the diagram, one step at a time.

Step 1 · without DISTINCT SELECT state returns one value per row: six towns, six states, with OR three times and WA twice. The faint columns are in the table but not in this SELECT.

Step 2 · spot the repeats Give each different value its own color. There are only three colors on the table: OR, WA, and ID.

Step 3 · DISTINCT removes repeats DISTINCT keeps one row for each different value and discards the rest. The faded rows are the repeats.

Step 4 · the result Six rows in, three rows out: the list of states that appear at all. Use it to learn what categories a column holds before you filter or group on it.

Step 5 · with two columns Same six rows, now selecting state, county. DISTINCT compares the whole row, not the first column. Springfield repeats Eugene's OR, Lane and is removed, but Albany's OR, Linn is a new pair, so it stays. Four rows out this time, not three.

Text version of this diagram

Step 1 · without DISTINCT. SELECT state returns one value per row: six towns, six states, with OR three times and WA twice. The faint columns are in the table but not in this SELECT.
Step 2 · spot the repeats. Give each different value its own color. There are only three colors on the table: OR, WA, and ID.
Step 3 · DISTINCT removes repeats. DISTINCT keeps one row for each different value and discards the rest. The faded rows are the repeats.
Step 4 · the result. Six rows in, three rows out: the list of states that appear at all. Use it to learn what categories a column holds before you filter or group on it.
Step 5 · with two columns. Same six rows, now selecting state, county. DISTINCT compares the whole row, not the first column. Springfield repeats Eugene's OR, Lane and is removed, but Albany's OR, Linn is a new pair, so it stays. Four rows out this time, not three.

Unique 1: What states are represented in our towns database?

Scenario: Before running state-specific analyses, you need to know which states are in the dataset.

Unique 2: Which counties appear as a secondary county for any town?

Scenario: Some towns span county lines. List each distinct county that shows up in the secondary_county column.

Counting

COUNT helps you understand the size of your data and answer “how many” questions.

COUNT comes in three forms, and they disagree as soon as a column has NULLs or repeats in it. Scroll to watch each one tally the same six rows.

COUNT(*), COUNT(column), and COUNT(DISTINCT column)

SELECT COUNT(*),
       COUNT(secondary_county),
       COUNT(DISTINCT secondary_county)
  FROM towns;

Scroll to build the diagram, one step at a time.

Step 1 · a column with gaps Six towns. Only three spill into a second county, so secondary_county is NULL (the dark cells) for the other three. Three ways to count give three answers.

Step 2 · COUNT(*) counts rows COUNT(*) counts rows and does not look inside them. NULL or not, every row adds one: 6.

Step 3 · COUNT(column) skips NULL COUNT(secondary_county) counts only the rows where that column has a value. The three NULL rows are skipped: 3.

Step 4 · COUNT(DISTINCT column) DISTINCT also skips repeats. Washington appears twice but counts once, so there are 2 different secondary counties.

Step 5 · three questions How many rows? COUNT(*). How many are filled in? COUNT(column). How many different values? COUNT(DISTINCT column). Pick the one that matches the question you were asked.

Text version of this diagram

Step 1 · a column with gaps. Six towns. Only three spill into a second county, so secondary_county is NULL (the dark cells) for the other three. Three ways to count give three answers.
Step 2 · COUNT(*) counts rows. COUNT(*) counts rows and does not look inside them. NULL or not, every row adds one: 6.
Step 3 · COUNT(column) skips NULL. COUNT(secondary_county) counts only the rows where that column has a value. The three NULL rows are skipped: 3.
Step 4 · COUNT(DISTINCT column). DISTINCT also skips repeats. Washington appears twice but counts once, so there are 2 different secondary counties.
Step 5 · three questions. How many rows? COUNT(*). How many are filled in? COUNT(column). How many different values? COUNT(DISTINCT column). Pick the one that matches the question you were asked.

Counting 1: How many towns are in our database?

Scenario: A journalist asks how comprehensive your database is. You need a quick count, reported as total_towns.

Counting 2: How many states does our county data cover?

Scenario: You want to verify your data covers both Oregon and Washington (and only those states). Report the count of distinct states as number_of_states.

Counting 3: How many towns lie entirely within a single county?

Scenario: Most towns never cross a county line. Count the towns with no secondary county, reported as towns_single_county.