Comparing two numbers that are approximately equal

Question

I have two tables, Table A and Table B. I have two attributes L1 and L2 for each table. I am trying output all the rows for both tables where L1 and L2 are equal for both tables. The problem is that L1 an L2 may differ my some small quantity. So when I run:

SELECT * FROM TableA l1 join TableB l2 on l1.L1 =l2.L1 and l1.L2 = l2.L2 

I get an empty set even though there are records that do match. How do I resolve this problem?

Example:

L1 = 118.4363 for Table A but for Table B L1 = 118.445428

Answer 1

Instead of checking for equality, check that the difference is below some threshold (e.g., 0.1, as in the example below).

SELECT * FROM 
    TableA l1, TableB l2 
WHERE 
    ABS(l1.L1-l2.L1) < 0.1 
    AND
    ABS(l1.L2-l2.L2) < 0.1

Answer 2

You will need to devise some tolerance, like say a difference of 0.01. Then compute the absolute value of the two when subtracted and see if it's within your tolerance

SET @tolerance_value = 0.01;
SELECT * 
FROM 
  TableA l1 join 
  TableB l2 
     on ABS(l1.L1 - l2.L1) < @tolerance_value and ABS(l1.L2 - l2.L2) < @tolerance_value;