I have two tables. One of them (user_profile table) has a field named "user_profile_id" and the other one (user_friend table) has two fields named "user_profile1_id" and "user_profile2_id" which are FK to the first table.

I want to check if there are any users in user_profile which are not in user_friend table, neither in user_profile1_id, nor user_profile2_id.

What query should I use?


CREATE TABLE user_profile (

CREATE TABLE user_friend (
  user_profile1_id INT,
  user_profile2_id INT

Use a LEFT JOIN and check for null values. Something like:

SELECT profile.* FROM user_profile profile
 LEFT JOIN user_friend friend1 ON friend1.user_profile1_id = user_profile_id
 LEFT JOIN user_friend friend2 ON friend2.user_profile2_id = user_profile_id
 WHERE friend1.user_profile1_id IS NULL AND friend2.user_profile2_id IS NULL

Here's a link to the various joins

| improve this answer | |
  • Is there any way without join? because user_friend table has 20 million records! – AliBZ Feb 24 '11 at 15:03
  • 2
    @alibz I think you seriously misunderstand databases. They are designed for working with sets, and JOIN is the most basic set op. – jcolebrand Feb 24 '11 at 15:06
  • thanx DTest, it worked great ;) – AliBZ Feb 24 '11 at 15:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.