mysql - mysql中的左连接查询

Question

我的 MySQL 数据库有问题。

我有两张 table 。

doTable_1

+------+----------+
| AREA | REGISTER |
+------+----------+
| AAAA | YS       |
| BBBB | YS       |
| CCCC | YS       |
| DDDD | YS       |
| EEEE | YS       |
| FFFF | YS       |
+------+----------+

doTable_2

+--------------+------+---------------+
| PREMIUM_AREA | AREA | NAME_AREA     |
+--------------+------+---------------+
| ZZZ          | GGGG | AREA BLAWED   |
| ZZZ          | FFFF | AREA BAYWOOD  |
| ZZZ          | AAAA | AREA BILLFOLD |
| ZZZ          | BBBB | AREA BEACHED  |
| ZZZ          | CCCC | AREA BASED    |
| ZZZ          | DDDD | AREA BANED    |
| ZZZ          | EEEE | AREA BAWD     |
| ZZZ          | HHHH | AREA ARBORED  |
| ZZZ          | LLLL | AREA BLAND    |
| ZZZ          | MMMM | AREA YSENA    |
| ZZZ          | NNNN | AREA AIRSHED  |
| ZZZ          | PPPP | AREA ALLOD    |
| ZZZ          | QQQQ | AREA BEEYARD  |
+--------------+------+---------------+

我需要这个输出，连接字段 AREA 的两个表以提取所有行:

+------+--------------+----------+
| AREA | PREMIUM_AREA | REGISTER |
+------+--------------+----------+
| AAAA | ZZZ          | YS       |
| BBBB | ZZZ          | YS       |
| CCCC | ZZZ          | YS       |
| DDDD | ZZZ          | YS       |
| EEEE | ZZZ          | YS       |
| GGGG | ZZZ          | NULL     |
| FFFF | ZZZ          | YS       |
| HHHH | ZZZ          | NULL     |
| LLLL | ZZZ          | NULL     |
| MMMM | ZZZ          | NULL     |
| NNNN | ZZZ          | NULL     |
| PPPP | ZZZ          | NULL     |
| QQQQ | ZZZ          | NULL     |
+------+--------------+----------+

尝试了这个查询，但输出错误，为什么？

mysql> SELECT
    AREA,
    PREMIUM_AREA,
    REGISTER
FROM
    `doTable_1` A
LEFT JOIN `doTable_2` CB ON A.AREA = CB.AREA
WHERE
    PREMIUM_AREA = 'ZZZ'
GROUP BY
    AREA
ORDER BY
    AREA ASC;
+------+--------------+----------+
| AREA | PREMIUM_AREA | REGISTER |
+------+--------------+----------+
| AAAA | ZZZ          | YS       |
| BBBB | ZZZ          | YS       |
| CCCC | ZZZ          | NULL     |
| DDDD | ZZZ          | NULL     |
| EEEE | ZZZ          | NULL     |
| GGGG | ZZZ          | NULL     |
| FFFF | ZZZ          | NULL     |
| HHHH | ZZZ          | NULL     |
| LLLL | ZZZ          | NULL     |
| MMMM | ZZZ          | NULL     |
| NNNN | ZZZ          | NULL     |
| PPPP | ZZZ          | NULL     |
| QQQQ | ZZZ          | NULL     |
+------+--------------+----------+
14 rows in set

Answer 1

使用LEFT JOIN，第一个表必须是包含您想要返回的所有行的表，第二个表是可能缺少行的表。所以应该是:

SELECT 
    A.AREA,
    PREMIUM_AREA,
    REGISTER
FROM
    `doTable_2` A
LEFT JOIN `doTable_1` CB ON A.AREA = CB.AREA
WHERE
    PREMIUM_AREA = 'ZZZ'
GROUP BY
    A.AREA
ORDER BY
    A.AREA ASC;

DEMO

或者您可以保持表的顺序相同，并使用RIGHT JOIN。