By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE EXAMPLE_TEST (ID NUMBER, VALUE NUMBER);
INSERT INTO EXAMPLE_TEST(ID, VALUE) VALUES (1, 1);
1 rows affected
INSERT INTO EXAMPLE_TEST(ID, VALUE) VALUES (2, 4);
1 rows affected
INSERT INTO EXAMPLE_TEST(ID, VALUE) VALUES (1, 9);
1 rows affected
INSERT INTO EXAMPLE_TEST(ID, VALUE) VALUES (2, 15);
1 rows affected
INSERT INTO EXAMPLE_TEST(ID, VALUE) VALUES (1, 17);
1 rows affected
SELECT *
FROM Example_test e1
JOIN Example_test e2
ON e1.ID = e2.id
AND ABS(e1.value-e2.value) < 10
AND e1.value ^= e2.value;
| ID | VALUE | ID | VALUE |
|---|---|---|---|
| 1 | 9 | 1 | 1 |
| 1 | 17 | 1 | 9 |
| 1 | 1 | 1 | 9 |
| 1 | 9 | 1 | 17 |