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

问题描述

目前我似乎无法弄清楚这一点.我试图连接两个表，只选择表 A 中在表 B 中没有匹配列的行.例如，假设我们有一个用户表和一个发送表.

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 表具有以下列:id, username
sent 表有以下列:id, username

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

我想从 users 中选择所有行，其中 username 在 sent 表中不存在.因此，如果 tom 在 users 和 sent 中，他将不会被选中.如果他在 users 但不在 sent 中，他将被选中.我试过了，但它根本不起作用:

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

推荐答案

试试这个 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 sent ON sent.id = users.id
WHERE sent.id IS NULL;

作为两个 id 字段，都将被索引(我原以为是主键)，因此此查询将比我建议的第一个查询得到更好的优化.

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屋！