Doing a join to search geolocation points in zones

I have a series of geolocation points in a table, and a series of "zones" in another; I'd like to do a join to identify which zone each point belongs in (if at all.)

Points table looks like:

+--+--------+---------+
|id|Latitude|Longitude|
+--+--------+---------+
| 1|37.23221|-81.32323|
+--+--------+---------+    
| 2|36.92321|-81.12423|
+--+--------+---------+
| etc                 |
+--+--------+---------+

Zones table looks like:

+--+------+------+------+-----+-----+
|id|Name  |LONG_E|LONG_W|LAT_N|LAT_S|
+--+------+------+------+-----+-----+
| 1|Zone 1| -81.0| -82.0| 38.0| 37.0|
+--+------+------+------+-----+-----+
| 2|Zone 2| -81.0| -82.0| 37.0| 36.0|
+--+------+------+------+-----+-----+
| etc                               |
+--+------+------+------+-----+-----+

I'd like to be able to query the points table and join the zones table, to get a result that looks like:

+----+----------+-----------+----+------+
|p.id|p.latitude|p.longitude|z.id|z.name|
+----+----------+-----------+----+------+
|   1|  37.23221|  -81.32323|   1|Zone 1|
+----+----------+-----------+----|------+    
|   2|  36.92321|  -81.12423|   2|Zone 2|
+----+----------+-----------+----+------+
|etc                                    |
+----+----------+-----------+----+------+

I'm pretty rusty on my SQL syntax, so I'm wondering if this is possible...I'm using MySQL BTW. Thanks!

This should do it:

SELECT p.id, latitude, longitude, z.id, z.name
  FROM points p
  LEFT JOIN zones z
    ON ( latitude BETWEEN LAT_S AND LAT_N
          AND
         longitude BETWEEN LONG_W AND LONG_E ) ;

[EDIT]

Per your comment, suppose you want to add a FK column to your points table that associates the point to its zone id. There are some reasons why this would be not recommended, such as if your zone boundaries could ever change, you would have incorrect/inconsistent data. However for raw performance, maybe you want the zone id denormalized into the points table, keeping in mind that it's like a cached zone id. The MIN() operator is there to protect you in case you have two zones overlapping. In that case, you've got bigger problems, but at least the UPDATE works, and picks the lower zone id by fiat.

ALTER TABLE points ADD (zone_id int references zone(id));

UPDATE points SET zone_id = 
( SELECT MIN(z.id)
  FROM zones z
  WHERE latitude BETWEEN LAT_S AND LAT_N
          AND
        longitude BETWEEN LONG_W AND LONG_E
);

StackExchange Database Administrators Q#68076, answer score: 3