Need help understanding this sql

I'm trying to decipher this SQL statement. Specifically, what locations z, locations o, locations a mean. What do z o and a mean in this case? Confused :(

SELECT o.zip_code
FROM locations z, locations o, locations a

WHERE z.zip_code = #{zip_code}
AND z.zip_code = a.zip_code
AND (3956 * (2 * ASIN(SQRT(
        POWER(SIN(((z.latitude-o.latitude)*0.017453293)/2),2) +
        COS(z.latitude*0.017453293) *
        COS(o.latitude*0.0174开发者_StackOverflow53293) *
        POWER(SIN(((z.longitude-o.longitude)*0.017453293)/2),2)
    )))) <= #{distance}

---

That is a Cartesian Product of the locations table with itself, twice. Since the query is joining the same table with itself it needs to rename each part so that they can be uniquely identified later in the query. z, o, and a are those names.

Look at example two here (http://www.fluffycat.com/SQL/Cartesian-Joins/) to see what is happening. Except in this case there are two cartesian products instead of one. All on the same table.

The query itself could potentially be quite slow since it is doing a double Cartesian product it will create a temporary table with n³ rows and (3m) columns where n is the number of rows in the location table and m is the number of columns.

Edit In response to comment. I'm not positive on this, but after reading your query I believe it is finding all zip codes that are withing a certain distance of another zip code. Itlooks like the third cartesian product (locations a) isn't even used so you could reduce the query to:

SELECT o.zip_code
FROM locations z, locations o

WHERE z.zip_code = #{zip_code}
AND (3956 * (2 * ASIN(SQRT(
        POWER(SIN(((z.latitude-o.latitude)*0.017453293)/2),2) +
        COS(z.latitude*0.017453293) *
        COS(o.latitude*0.017453293) *
        POWER(SIN(((z.longitude-o.longitude)*0.017453293)/2),2)
    )))) <= #{distance}

But that still has one Cartesian product. It'd be better if we could get rid of it to:

SELECT zip_code
FROM locations

WHERE (3956 * (2 * ASIN(SQRT(
        POWER(SIN((((SELECT latitude FROM locations WHERE zip_code = #{zip_code})-latitude)*0.017453293)/2),2) +
        COS((SELECT latitude FROM locations WHERE zip_code = #{zip_code})*0.017453293) *
        COS(latitude*0.017453293) *
        POWER(SIN((((SELECT longitude FROM locations WHERE zip_code = #{zip_code})-longitude)*0.017453293)/2),2)
    )))) <= #{distance}

Will probably be optimized quite well by MySQL

---

It is shorthand for

 locations AS z, locations AS o, locations AS a

Or in other words they are aliases.

Rather than keep writing locations throughout their code the original author has chosen to alias them to shorthand, in this case z o and a.

In addition because he is using the same table three times the aliasing allows him to differentiate between each one

---

these are table alias...

Aliases are powerful for complex queries that need to 
    use the same table twice but in different ways.

In this query, the FROM clause specifies aliases for the table names. The alias location z means than the location table can be referred to as z elsewhere in the query. For example, z.zip_code is the same as location.zip_code. This saves typing in this query.

REFERENCE

---

• z holds the record you lookup using #{zip_code} (reference point)

• a is the same record as z

• o contains all records that are within the geographical distance of #{distance}

in short:

It finds you all zip codes that are in a circle with the radius of #{distance} around z

By the way, 3956 is the radius of the earth in miles (approximately), so the result you get will be in miles ;)

If you are interested in this, you can read more on http://www.movable-type.co.uk/scripts/gis-faq-5.1.html

---

It looks like its trying to select all the zipcodes within #{distance} of the zipcode ${zip_code}.

I thinkt his post gives a good explanation of the law of cosines being used for this exact purpose: http://blog.cleverly.com/archive/2000-03.html

the z, o, and a are alias, because the same table is being joined 3 times to itself.

---

These are shorthand for table names for so instead of typing locations.zip_code it is abbreviated to a.zip_code

It also helps in this exampe as the same table is selected from 3 times.

---

The letters are table alias, they are essentially nicknames that the SQL dev has given the location table. The same locations table is joined together using three different names, because it is one table to differntiate between the different uses of the locations table

locations has been renamed to z, locations has been renamed to o, locations has been renamed to a

Change z to locations1 Change o to locations2 and change a to locations3 if this helps you read the SQL