主页 / dba / 问题 / 20009
Accepted
Alireza
Asked: 2012-06-28 01:46:19 +0800 CST

我应该使用 WHERE NOT EXISTS 还是 LEFT JOIN 来获取关系表的 ID？

  • 772

我有users表： 

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

还有一个关系表： 

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

我想获取那些不在特定用户圈子中的用户（如 G+）。这意味着用户尚未将这些人添加到他/她的圈子中。我尝试了以下查询，但它是空的： 

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

以下查询返回 3 条记录： 

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

john现在应该不会显示以 uid=60 添加的用户名！此查询的结果为空。我想不通，我做错了什么？

mysql

1 个回答

  1. Best Answer

    子查询应该是相关的：

    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
     ) ;

    （与问题无关）

    谁告诉你使用保留字to和from作为列名或表名是个好主意，谁值得一试。

    您可以使用 aLEFT JOIN / IS NULL或NOT IN查询获得相同的结果：

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

相关问题