title: Using BETWEEN SYMMETRIC for checking 2 row value expressions
body: <p>I would like to compare two columns values from <code>Table-1</code> (say <code>colA, colX</code>) to check if these are in the range of values for columns in <code>Table-2</code> (say <code>colB, colD, colY, colZ</code>). That's, if <code>colA</code> is between <code>colB-colD</code>, and/or <code>colX</code> is between <code>colY-colZ</code> respectively. </p>
  
  <p>I understand <code>BETWEEN SYMMETRIC</code> is used when order is irrelevant, e.g. (min, max) or (max,min). Below is the example I created.</p>
  
  <p>TABLES:</p>
  
  <pre><code>CREATE TABLE location (id int, lat float, lon float, gpstime int);
  CREATE TABLE trips( trip_id int, lat_start float, lat_end float,
                 lon_start float, lon_end float, travel_mode text);
  </code></pre>
  
  <p>TABLE VALUES:</p>
  
  <pre><code>INSERT INTO location
  SELECT 1, 41.2, 8.3, 11111 UNION ALL   --both lat and lon in the interval
  SELECT 2, 43.3, 8.5, 22222 UNION ALL   --only lat meets criteria
  SELECT 3, 44.0, 8.5,  33333            --only lon meets criteria
  
  INSERT INTO trips
  SELECT 1, 41.1, 41.9, 8.0, 8.8, 'foot' UNION ALL  
  SELECT 2, 43.1, 43.5, 9.5, 8.9, 'bus' UNION ALL
  SELECT 3, 42.1, 42.8, 8.7, 8.0, 'metro' 
  </code></pre>
  
  <p><strong>TASK:</strong></p>
  
  <p>The goal is to get all rows for which either <code>lat</code> or <code>lon</code> (or both) in <code>location</code> table happen to satisfy the criteria (in <code>trips</code> table): <code>lat</code> between <code>lat_start-lat_end</code>; <code>lon</code> between <code>lon_start- lon_end</code>.</p>
  
  <p>Clearly all rows of <code>location</code> table should be returned: row 1 meets all and rows 2 and 3 meet either criteria. </p>
  
  <p>Expected result:</p>
  
  <pre><code>+---------+-----------+------+-----+-------------+
  | trip_id | timestamp | lat  | lon | travel_mode |
  +---------+-----------+------+-----+-------------+
  |       1 |     11111 | 41.2 | 8.3 | foot        |
  |       2 |     22222 | 43.1 | 8.5 | bus         |
  |       3 |     33333 | 44.0 | 8.5 | metro       |
  +---------+-----------+------+-----+-------------+
  </code></pre>
  
  <p>So I tried different queries using <code>BETWEEN SYMMETRIC</code> but the results aren't correct.</p>
  
  <p>Query-1:</p>
  
  <pre><code>SELECT trip_id, gpstime AS timestamp, lat, lon,  travel_mode
  FROM location 
  INNER JOIN trips 
     ON (lat BETWEEN SYMMETRIC lat_start AND lat_end) 
     AND (lon BETWEEN SYMMETRIC lon_start AND lon_end) 
  </code></pre>
  
  <p>Query result:</p>
  
  <pre><code>+---------+-----------+------+-----+-------------+
  | trip_id | timestamp | lat  | lon | travel_mode |
  +---------+-----------+------+-----+-------------+
  |       1 |     11111 | 41.2 | 8.3 | foot        |
  +---------+-----------+------+-----+-------------+
  </code></pre>
  
  <p>Query-2: messed up everything</p>
  
  <pre><code>SELECT trip_id, gpstime AS timestamp, lat, lon,  travel_mode
  FROM location 
  INNER JOIN trips 
     ON (lat, lon) BETWEEN SYMMETRIC (lat_start,  lat_end) AND (lon_start, lon_end)
  </code></pre>
  
  <p>Result:</p>
  
  <pre><code>+---------+-----------+------+-----+-------------+
  | trip_id | timestamp | lat  | lon | travel_mode |
  +---------+-----------+------+-----+-------------+
  |       2 |     11111 | 41.2 | 8.3 | foot        |
  |       3 |     11111 | 41.2 | 8.3 | metro       |
  +---------+-----------+------+-----+-------------+
  </code></pre>
  
  <p>Query-3: worst even</p>
  
  <pre><code>SELECT trip_id, gpstime AS timestamp, lat, lon,  travel_mode
  FROM location 
  INNER JOIN trips 
     ON lat BETWEEN SYMMETRIC lat_start AND lat_end
  UNION 
  SELECT trip_id, gpstime AS timestamp, lat, lon,  travel_mode
  FROM location 
  INNER JOIN trips 
     ON lon BETWEEN SYMMETRIC lon_start AND lon_end
  </code></pre>
  
  <p>Result:</p>
  
  <pre><code>+---------+-----------+------+-----+-------------+
  | trip_id | timestamp | lat  | lon | travel_mode |
  +---------+-----------+------+-----+-------------+
  |       1 |     11111 | 41.2 | 8.3 | foot        |
  |       1 |     22222 | 43.3 | 8.5 | foot        |
  |       1 |     33333 | 44   | 8.5 | foot        |
  |       2 |     22222 | 43.3 | 8.5 | bus         |
  |       3 |     11111 | 41.2 | 8.3 | metro       |
  |       3 |     22222 | 43.3 | 8.5 | metro       |
  |       3 |     33333 | 44   | 8.5 | metro       |
  +---------+-----------+------+-----+-------------+
  </code></pre>
  
  <p><strong>Question:</strong> What is the correct query to achieve the desired result?</p>
  
  <p><strong>EDIT:</strong></p>
  
  <p>Based on answers received, recommending use of <code>OR</code> instead of <code>AND</code> in Query-1, I edit my question below to show the result of the recommended query:</p>
  
  <p>QUERY:</p>
  
  <pre><code>SELECT trip_id, gpstime AS timestamp, lat, lon,  travel_mode
  FROM location 
  INNER JOIN trips 
     ON (lat BETWEEN SYMMETRIC lat_start AND lat_end) 
     OR (lon BETWEEN SYMMETRIC lon_start AND lon_end);
  </code></pre>
  
  <p>And here goes the query results:</p>
  
  <pre><code>+---------+-----------+------+-----+-------------+
  | trip_id | timestamp | lat  | lon | travel_mode |
  +---------+-----------+------+-----+-------------+
  |       1 |     11111 | 41.2 | 8.3 | foot        |
  |       3 |     11111 | 41.2 | 8.3 | metro       |
  |       1 |     22222 | 43.3 | 8.5 | foot        |
  |       2 |     22222 | 43.3 | 8.5 | bus         |
  |       3 |     22222 | 43.3 | 8.5 | metro       |
  |       1 |     33333 | 44   | 8.5 | foot        |
  |       3 |     33333 | 44   | 8.5 | metro       |
  +---------+-----------+------+-----+-------------+
  </code></pre>