问题描述
到目前为止,我似乎无法想象这一点。我试图加入两个表,只选择表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选择其他表中没有匹配列的行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!