Should I use WHERE NOT EXISTS or LEFT JOIN for getting the ids of relationship table? I have users table:  

CREATE TABLE `users` (
 `uid` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
 `email` varchar(70) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `flname` varchar(60),
 PRIMARY KEY (`uid`)
) ENGINE=InnoDB


And a relationship table:  

CREATE TABLE `relationship` (
 `rid` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `from` mediumint(8) unsigned NOT NULL,
 `to` mediumint(8) unsigned NOT NULL,
 PRIMARY KEY (`rid`),
 UNIQUE KEY `from_2` (`from`,`to`),
 KEY `to` (`to`),
 CONSTRAINT `relationship_ibfk_1` FOREIGN KEY (`from`) REFERENCES `users` (`uid`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `relationship_ibfk_2` FOREIGN KEY (`to`) REFERENCES `users` (`uid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB


I want to fetch those users that is not in a specific user's circle (Like G+). It means that user has not yet added those people to his/her circle. I tried the below query but it was empty:  

SELECT uid FROM users WHERE flname LIKE'%john%' AND NOT EXISTS (Select `to` FROM relationship WHERE `from`=60)  


The below query returns 3 records:   

SELECT uid,flname FROM users WHERE flname LIKE'%john%' 


Now users in the name of john who has been added by uid=60 should not have be shown! The result of this query is empty. I can't figure that out, what I'm doing wrong?
The subquery should be correlated:

SELECT uid 
FROM users 
WHERE flname LIKE '%john%' 
  AND NOT EXISTS 
      ( SELECT *              --- doesn't matter what you put here for EXISTS subqueries
        FROM relationship 
        WHERE `from` = 60
          AND `to` = users.uid        --- this line added
     ) ;




(unrelated to the issue)

Whoever told you that it's a good idea to use reserved words like to and from as column or table names, deserves a kick.



You can have the same results with a LEFT JOIN / IS NULL or a NOT IN query:

SELECT uid 
FROM users 
WHERE flname LIKE '%john%' 
  AND uid NOT IN 
      ( SELECT `to` 
        FROM relationship 
        WHERE `from` = 60
     ) ;