How to find different values ​​in SQL table?

Question

SQL

There is a table in Sqlite with such columns: domain, key, value
domain can only take the values ​​"first" and "second".
You need to find such pairs of strings in which
a) the key was the same
b) one string in the pair domain == "first", the second domain == "second"
c) value would be different
(There was an idea to use the aggregate function "standard deviation ”, but sqlite3 does not have it)

Answer 1

SELECT *
  FROM `table` AS `t1`
  JOIN `table` AS `t2`
    ON `t2`.`key` = `t1`.`key`
      AND `t1`.`domain` = 'первый'
      AND `t2`.`domain` = 'второй'
      AND `t2`.`value` != `t1`.`value`