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

Question

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.

Answer 1

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

Answer 2

You can also use this:

select 

   -- add other columns as needed
   (a.lastname,a.gender) 
=  (b.lastname,a.gender) as similar,


  a.lastname as a_lastname,
  a.firstname as a_firstname,
  a.age as a_age,

  'x' as x,

  b.lastname as b_lastname,
  b.firstname as b_firstname,
  b.age as b_age



from person a
cross join person b
where a.id = 1 and b.id = 2

Output:

SIMILAR A_LASTNAME A_FIRSTNAME A_AGE X B_LASTNAME B_FIRSTNAME B_AGE
1       Lennon     John        40    x Lennon     Julian      15

Live test: http://www.sqlfiddle.com/#!2/840a1/2