Take the 2-minute tour ×

I have the following table for storing user data:


TABLE: users
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


    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.

share|improve this question

4 Answers 4

up vote 4 down vote accepted

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    )
  users u1,
  users u2
WHERE u1.id = x
  AND u2.id = y
share|improve this answer
Excellect - thank you Peter, this is what I was looking for ! –  Sydney Boy May 9 '12 at 5:57

You can also use this:


   -- add other columns as needed
=  (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


1       Lennon     John        40    x Lennon     Julian      15

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

share|improve this answer

You can count the number of users with the same columns using group by:

select  u1.maritalStatus
,       u1.gender
,       u1.occupation
,       count(*)
from    users u1
group by
,       u1.gender
,       u1.occupation
share|improve this answer
Thanks for your response - however I need a query that specifically compares two given users. Your query simply lists the no. of users with a specific value. I would need to also specify which users I am comparing with a WHERE clause (e.g. WHERE userid = 87 AND userid = 88) - hope thats clear. –  Sydney Boy May 8 '12 at 7:06
Well basicly the relevant row would give the value 2 if both users have the same maritalStatus, gender, occupation. You could add a WHERE u1.user_id IN (id1, id2) and see if there's one row with the value 2 returned. –  Robin Castlin May 8 '12 at 7:07
@SydneyBoy: I think Peter Lang's answer might be what you're looking for? –  Andomar May 8 '12 at 7:09

Just a continued example of Peter Langs suggestion in PHP:

$arr_cols   = array('martialStatus', 'gender', 'occupation');
$arr_where = array();
$arr_select = array();
foreach($arr_cols as $h) {

    $arr_having[] = "compare_{$h}";
    $arr_select[] = "(u1.{$h} != u2.{$h}) AS compare_{$h}";


$str_having  = implode(' + ', $arr_where);
$str_select = implode(', ', $arr_where);

$query = mysql_query("
SELECT {$str_select}
FROM users AS u1, users AS u2
WHERE u1.userid = {$int_userid_1} AND u2.userid = {$int_userid_2}
HAVING {$str_having} > 0

/* Having case can be removed if you need the row regardless. */

/* Afterwards you check these values: */

$row = mysql_fetch_assoc($query);
foreach($arr_cols as $h)
    if ($row["compare_{$h}"])
         echo "Found difference in column {$h}!";
share|improve this answer

Your Answer


