4

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?

Improve this question

3 Answers 3

Reset to default
11

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Nicely and succinctly explained. I would also add that NOT IN starts to degrade performancew-wise as the subquery returns a greater number of records. Not Exists may be better if the profiles table is very large.
3

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.

Improve this answer

7 Comments

the query just keep loading, nothing happends, i have a very strong computer, soemthing must be wrong.
Thats because of the scale of your data, and likely because you don't have indexes on profileid in either table. If you can edit the tables try adding indexes.
ABout the index thing, this is not how i usally do thing, i only use the id table for relating tables, but this is just temporary so i did things diffrently and forgot to add index, if it still takes time, maybe i can do two querys, store in array, compare arrays and then store all data in a third array,
after adding indexes it went well, but i got to many results, let my try again wait
the query works well now, but i get duplicate results or more of the same profileid, who do i sort them out.
|
0 
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.)

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.