如何在MySQL查询中不存在或不存在内部联接? [英] How to inner join with not in or not exist in mysql query?

问题描述

我有一些这样的数据 给用户

I have some data like this for user

id | username | email
1  | test     | [e-mail]
2  | om       | [e-mail]
3  | aa       | [e-mail]

我有这样的数据用户生物数据

And I have data user biodata like this

id | username | bio
1  | test     | test
2  | om       | test2

因此，我想在内部联接中不使用inc来显示where user.username not in biodata.username的数据，我尝试这样做，但这是错误的

So, I want use not in with inner join for showing data with where user.username not in biodata.username and I try like this but it's error

select user.*, biodata.* from user inner join biodata on user.username = biodata.username where user.username not in biodata.username;

那么，如何使用呢?

推荐答案

这需要常见的LEFT JOIN ... IS NULL模式.

SELECT u.id, u.username, u.email
  FROM user u
  LEFT JOIN biodata b ON u.username = b.username
 WHERE b.id IS NULL

LEFT JOIN操作将保留user中的所有行，无论它们在biodata中是否匹配. (相反，普通的JOIN会抑制user中没有匹配项的行.)b.id IS NULL操作会过滤出要做有匹配项的行.

The LEFT JOIN operation preserves all rows in user, whether or not they have matches in biodata. (By contrast, an ordinary JOIN would suppress rows from user that didn't have matches.) The b.id IS NULL operation filters out the rows that do have matches.

这篇关于如何在MySQL查询中不存在或不存在内部联接?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！