MySQL选择其他表中没有匹配列的行 [英] MySQL select rows that do not have matching column in other table

问题描述

到目前为止，我似乎无法想象这一点。我试图加入两个表，只选择表A中没有表B中匹配列的行。例如，假设我们有一个用户表和一个已发送的表。

用户表具有以下列： id，username

发送表具有以下列： id，username

我想从用户中选择所有行，其中用户名不存在于发送表。所以，如果 tom 在用户和发送他将不会被选中。如果他在用户而不是发送，他将被选中。我试过这个，但它根本没有工作：

  SELECT pooltest.name，senttest.sentname 
 FROM pooltest ，senttest 
 WHERE pooltest.name！= senttest.sentname 
  

解决方案

尝试这个SQL：

  SELECT users.username 
 FROM users 
 LEFT JOIN sent ON sent.username = users.username 
 WHERE sent.username IS NULL; 
  

我认为更好的方法是：

  SELECT users.username 
 FROM users 
 LEFT JOIN发送ON sent.id = users.id 
 WHERE sent.id IS NULL; 
  

作为这两个id字段，将被索引（主键我会想到），所以这个查询将比我建议的第一个更好的优化。

然而，您可能会发现我的第一个建议更适合您，这取决于您的应用程序的要求。 >

I can't seem to figure this out so far. I am trying to join two tables and only select the rows in table A that do not have a matching column in table B. For example, lets assume we have a users table and a sent table.

users table has the following columns: id, username
sent table has the following columns: id, username

I want to select all rows from users where username does not exist in sent table. So, if tom is in users and in sent he will not be selected. If he is in users but not in sent he will be selected. I tried this but it didn't work at all:

SELECT pooltest.name,senttest.sentname 
FROM pooltest,senttest 
WHERE pooltest.name != senttest.sentname

解决方案

Try this SQL:

SELECT users.username
FROM  users
LEFT JOIN sent ON sent.username = users.username
WHERE sent.username IS NULL;

The better way in my opinion would be:

SELECT users.username
FROM  users
LEFT JOIN sent ON sent.id = users.id
WHERE sent.id IS NULL;

As both the id fields, would be indexed (primary key I would have thought) so this query would be better optimised than the first one I suggested.

However you may find my first suggestion better for you, it depends on what your requirements are for your application.

这篇关于MySQL选择其他表中没有匹配列的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！