{Complexity}

The great circle distance formula:
d = acos(sin(x1)*sin(x2)+cos(x1)*cos(x2)*cos(y2-y1)) * r

CREATE TABLE ZCTA (
zcta CHAR(6) NOT NULL
, lat_degrees DECIMAL(9,6) NOT NULL
, long_degrees DECIMAL(9,6) NOT NULL
, PRIMARY KEY(zcta));

ALTER TABLE ZCTA
ADD COLUMN lat_radians DECIMAL(9,6) NOT NULL
, ADD COLUMN long_radians DECIMAL(9,6) NOT NULL;

UPDATE ZCTA
SET lat_radians = lat_degrees * (PI() / 180)
, long_radians = long_degrees * (PI() / 180);

/* calculating the distance between two points */
SELECT
ROUND(ACOS(SIN(x1.lat_radians) * SIN(x2.lat_radians)
+ COS(x1.lat_radians) * COS(x2.lat_radians)
* COS(x2.long_radians – x1.long_radians)) * 3956, 2) AS “Distance”
FROM ZCTA x1, ZCTA x2
WHERE x1.zcta = ’1001′
AND x2.zcta = ’21236′;

/* zip codes within a given radius */
SELECT
x2.zcta AS zip
, ROUND(ACOS(SIN(x1.lat_radians) * SIN(x2.lat_radians)
+ COS(x1.lat_radians) * COS(x2.lat_radians)
* COS(x2.long_radians – x1.long_radians)) * 3956, 2) AS “Distance”
FROM ZCTA x1, ZCTA x2
WHERE x1.zcta = ’21236′
AND ACOS(SIN(x1.lat_radians) * SIN(x2.lat_radians)
+ COS(x1.lat_radians) * COS(x2.lat_radians)
* COS(x2.long_radians – x1.long_radians)) * 3956 <= 5
ORDER BY Distance;

/* StoreLocation table definition */
CREATE TABLE StoreLocation (
store_id INT NOT NULL AUTO_INCREMENT
, address VARCHAR(100) NOT NULL
, city VARCHAR(35) NOT NULL
, state CHAR(2) NOT NULL
, zip VARCHAR(6) NOT NULL
, PRIMARY KEY (store_id)
, KEY (zip));

/* zip codes within a given radius  with StoreLocation table*/
SELECT
address
, city
, state
, zip
FROM StoreLocation
WHERE zip IN (
SELECT x2.zcta
FROM ZCTA x1, ZCTA x2
WHERE x1.zcta = ’21236′
AND ACOS(SIN(x1.lat_radians) * SIN(x2.lat_radians)
+ COS(x1.lat_radians) * COS(x2.lat_radians)
* COS(x2.long_radians – x1.long_radians)) * 3956 <= 5
);

SELECT
address
, city
, state
, zip
FROM StoreLocation s1
INNER JOIN (
SELECT x2.zcta
FROM ZCTA x1, ZCTA x2
WHERE x1.zcta = ’21236′
AND ACOS(SIN(x1.lat_radians) * SIN(x2.lat_radians)
+ COS(x1.lat_radians) * COS(x2.lat_radians)
* COS(x2.long_radians – x1.long_radians)) * 3956 <= 5
) AS zips
ON s1.zip = zips.zcta;

SELECT
address
, city
, state
, zip
FROM ZCTA x1, ZCTA x2
INNER JOIN StoreLocation s1
ON x2.zcta = s1.zip
WHERE x1.zcta = ’21236′
AND ACOS(SIN(x1.lat_radians) * SIN(x2.lat_radians)
+ COS(x1.lat_radians) * COS(x2.lat_radians)
* COS(x2.long_radians – x1.long_radians)) * 3956 <= 5;

SELECT
address
, city
, state
, zip
, ROUND(ACOS(SIN(x1.lat_radians) * SIN(x2.lat_radians)
+ COS(x1.lat_radians) * COS(x2.lat_radians)
* COS(x2.long_radians – x1.long_radians)) * 3956, 2) AS “Distance”
FROM ZCTA x1, ZCTA x2
INNER JOIN StoreLocation s1
ON x2.zcta = s1.zip
WHERE x1.zcta = ’21236′
AND ACOS(SIN(x1.lat_radians) * SIN(x2.lat_radians)
+ COS(x1.lat_radians) * COS(x2.lat_radians)
* COS(x2.long_radians – x1.long_radians)) * 3956 <= 5
ORDER BY Distance;

Theta Style:
SELECT i.order_id, p.product_id, p.name, p.desc
FROM CustomerItem i, Product p
WHERE i.product_id = p.product_id
AND i.order_id = 84463;

ANSI Style:
SELECT i.order_id, p.product_id, p.name, p.desc
FROM CustomerItem i
INNER JOIN Product p ON i.product_id = p.product_id
WHERE i.order_id = 84463;

Sometimes, it’s hard to realized whether there is other style of SQL. Obviously there are two different SQL style. theta is older and more obscure but many people still use it.