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

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.