How to efficiently check if two columns in the same table is one-to-one mapping?

Question

Even though the distinct value counts equals, it did not necessarily mean an one-to-one mapping relation.

SELECT COUNT(DISTINCT [Column_A]) FROM MyTable 
SELECT COUNT( DISTINCT [Column_B]) FROM MyTable 

Column A: 1 2 3 4 5 6 7 8 9 10 1
Column B: a b c d e f g h i j j

The query above return a value of 10 for each column but they are not one-to-one mapping.

How can I exactly check the existence of this matching type? Thanks in advance

Answer 1

This query will show you any values for [Column_A] which have multiple values for [Column_B]. If it returns no rows, you have a 1 to 1 relationship.

select [Column_A], count([Column_B]) as not_distinct
from (select [Column_A], [Column_B] from MyTable
      group by [Column_A], [Column_B]) subtable
group by [Column_A]
having count([Column_B]) >1;

Answer 2

Assume all values are not NULL. The query result shows the value violates 1 to 1 relation.

-- Check Column A value which DOES NOT maps to exactly 1 Column_B value
SELECT Column_A, COUNT(Column_B) FROM MyTable GROUP BY Column_A HAVING COUNT(Column_B) > 1

-- Check Column B value which DOES NOT maps to exactly 1 Column_A value
SELECT Column_B, COUNT(Column_A) FROM MyTable GROUP BY Column_B HAVING COUNT(Column_A) > 1