mysql - 将第二个 MySQL 表中的数据添加到第一个表的结果中

Question

我有 2 张 table :

+-----------+
|   users   |
+-----------+
|   John    |
|   Peter   |
|   Alex    |
+-----------+

和

+-----------+
|   banned  |
+-----------+
|   John    |
|   Peter   |
+-----------+

是否可以SELECT * FROM users并将结果与​​第二个表相结合，以确定用户是否被禁止？

Answer 1

您可以使用 LEFT JOIN 来使用以下内容:

-- with CASE WHEN
SELECT users.*, CASE WHEN banned.username IS NULL THEN 0 ELSE 1 END AS isBanned
FROM users LEFT JOIN banned ON users.username = banned.username

-- without CASE WHEN (thanks to @forpas!)
SELECT users.*, banned.username IS NOT NULL AS isBanned
FROM users LEFT JOIN banned ON users.username = banned.username

如果用户与banned表不匹配，则用户名(以及其他列)为NULL。您可以使用CASE WHEN来检查用户名是否为NULL。

您还可以使用EXISTS而不是 LEFT JOIN 来获取此附加信息:

SELECT *, EXISTS(SELECT 1 FROM banned WHERE users.username = banned.username) AS isBanned
FROM users

还有第三个选项，使用 IF :

SELECT users.*, IF(banned.username IS NULL, 0, 1) AS isBanned
FROM users LEFT JOIN banned ON users.username = banned.username

demo on dbfiddle.uk