SQL Query for Finding ZCTAs Present in Multiple States

Using the previously described 2010 US Census SQLite database I created, there are a whole slew of neat things that can be learned fairly easily. For example, I was curious which ZCTAs are contained in more than one state. Hammerite from Something Awful created a really nice SQL query for solving this problem in this post.

1
2
3
4
5
6
7
8
9
10

SELECT DISTINCT
	zctas.zcta, states.state
FROM
	states_zctas
	JOIN states_zctas AS states_zctas_different ON
		states_zctas.zcta_id = states_zctas_different.zcta_id AND
		states_zctas.state_id <> states_zctas_different.state_id
	JOIN zctas ON states_zctas.zcta_id = zctas.id
	JOIN states ON states_zctas.state_id = states.id
ORDER BY zctas.zcta ASC;

The results look like this:

So, as it turns out, there are 103 ZCTAs that are present in more than 1 state. Unsurprisingly, there’s no ZCTA present in more than two states.