mysql query WHERE in one table but not the other

Question

EDIT:This did it:

  SELECT DISTINCT profileid 
FROM profilesrelevation 
WHERE profileid NOT IN (
  SELECT profileid FROM profiles
)

I need to get the profileid values that exist in profilesrelevation table but not in profiles table

profiles table have 8107 diffrent "profileid" values while profilesrelevation table has 8380 diffrent "profilesid" values...

profiles.profileid
profilesrelevation.profileid

select * from profiles, profilesrelevation 
where profilesrelevation.profileid != profiles.profileid  

does not work , how?

Answer 1

You'll be wanting to use a set:

SELECT DISTINCT profileid 
FROM profilesrelevation 
WHERE profileid NOT IN (
  SELECT profileid FROM profiles
)

This selects all rows/columns from table:profilesrelevation where the profileid of the row isn't also in table:profiles :)

Updated: include distinct since it would appear profileid isn't unique in the profilesrelevation table.

Answer 2

SELECT
    profilesrelevation.profileid
FROM
    profilesrelevation
    LEFT JOIN profiles ON profilesrelevation.profileid = profiles.profileid
WHERE
    profiles.profileid IS NULL

(You may wish to use SELECT DISTINCT.)

Answer 3

Using LEFT JOIN / IS NULL

   SELECT pr.*
     FROM PROFILESREVELATION pr
LEFT JOIN PROFILES ON p.profileid = pr.profileid
    WHERE p.profileid IS NULL

Using NOT EXISTS

SELECT pr.*
  FROM PROFILESREVELATION pr
 WHERE NOT EXISTS(SELECT NULL
                    FROM PROFILES p
                   WHERE p.profileid = pr.profileid)

Using NOT IN

SELECT pr.*
  FROM PROFILESREVELATION pr
 WHERE pr.profileid NOT IN (SELECT p.profileid
                              FROM PROFILES p)

Conclusion

The LEFT JOIN IS NULL is the most efficient on MySQL when the columns compared are not nullable. If the columns compared were nullable, NOT IN and NOT EXISTS are more efficient.