SQL compare two rows in a table to find how many values are different

mysql, sql

Solution

This will select the number of columns that are not the same for user `x` and user `y`:

SELECT ( u1.martialStatus <> u2.martialStatus )
     + ( u1.gender        <> u2.gender        )
     + ( u1.occupation    <> u2.occupation    )
FROM
  users u1,
  users u2
WHERE u1.id = x
  AND u2.id = y

Problem

I have the following table for storing user data: e.g. ``` TABLE: users COLUMNS: ... maritalStatus (INT) - FK gender (CHAR) occupation (INT) - FK ... ``` Now I want to compare two users in this table to see how many columns match for any two given users (say user X & user Y) I am doing it via mySQL Stored Procedures by getting each value separately and then comparing them e.g. ``` SELECT maritalStatus from users where userID = X INTO myVar1; SELECT maritalStatus from users where userID = Y INTO myVar2; IF myVar1 = myVar2 THEN ... END IF; ``` Is there a shorter way using an SQL query where I can compare two rows in a table and see which columns are different? I dont need to know how much different they actually are, just need to know if they contain the same value. Also I will only be comparing selected columns, not every column in the user table.

Original source