3

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?

5
  • May I ask what karbar contains? Commented Jun 27, 2012 at 9:58
  • It was a typo, sorry. It's now correct.
    – Alireza
    Commented Jun 27, 2012 at 10:01
  • 1
    On the other hand, I think you missed a condition in your subquery specifying that to equals users.uid. Commented Jun 27, 2012 at 10:02
  • I don't think so! from=60 is the conditional that I need: from=uid
    – Alireza
    Commented Jun 27, 2012 at 10:05
  • But you say here that you want all Johns while there are no relationships originating from user 60 (be it John or Paulette or whatever). Commented Jun 27, 2012 at 10:10

1 Answer 1

6

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
     ) ;
5
  • Good point about reserved keys, I'll change them in my project.
    – Alireza
    Commented Jun 27, 2012 at 11:24
  • What are the performance considerations here? Which one is better, and which one is worse in terms of performance? Is there a general rule, where you shouldn't use one or the other for such purpose. Commented Sep 10, 2023 at 12:58
  • @MichaelKruglos the question was about writing a query to get wanted results, not about performance. That would need an essay to write ;) Commented Sep 11, 2023 at 8:06
  • 1
    In short, I avoid IN and NOT IN subqueries in MySQL as (besides performance which is often not good) it's easy to get wrong results due to NULL values. LEFT JOIN and NOT EXISTS usually end up to having the same or similar execution plan so performance would be the same. I tend to prefer NOT EXISTS as queries are more readable for me, especially when there are many conditions / many NOT EXISTS subqueries / many LEFT JOIN - IS NULL checks. Commented Sep 11, 2023 at 8:11
  • Note however the "usually". It all depends on various other things like "is it one subquery or many", what version of MySQL, how big are the tables (main table and tables in the subquery), what indexes there are, what are the datatypes, etc. Commented Sep 11, 2023 at 8:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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