如何在 MySQL 中連接兩個表並顯示一個查詢結果？

我有一個 table1（記錄 3）和 table2（記錄 3）。我在兩者都有欄位名稱的地方。現在我想從這兩個表中得到一個結果，它將顯示兩個表記錄，如果有重複則只取一個。所以我預期的輸出記錄將包含 5 行而不是 6 行。我怎麼做？

例子：

table1:                       table2:

+-------------------------+   +--------------------------------+
| Name                        | Name
---------------------------   +---------------------------------
| A    |                      | C    |
| B    |                      | D    |
| C    |                      | E    |


My Expected output is:

+-------------------------+
| Name | ID                
---------------------------
| A    | 1 table1
| B    | 2 table1
| C    | 3 table2 or table1 (no unique)
| D    | 4 table2
| E    | 5 table2

我試過這個：

SELECT 
 name as name 
FROM 
 table1
UNION
SELECT 
 anothernamename as name 
FROM 
 table2
WHERE
  name like '%C%'

Error: #1054 - Unknown column 'name' in 'where clause'

**跟進：**與 Where 子句聯合 + 特大要求。

SELECT * FROM 
(
   ( 
    SELECT * FROM table1 
    WHERE ...
    ORDER BY ...
    LIMIT ...
   ) 
UNION 
   ( 
    SELECT * FROM table2 
    WHERE ...
    ORDER BY ...
    LIMIT ...
   )
) as t
WHERE ...
   ORDER BY ...

這是您需要的查詢：

SELECT name FROM
(
   SELECT name FROM table1
   UNION
   SELECT name FROM table2
) A;

以下是基於您的問題的一些範常式式碼：

use test
drop table if exists table1;
drop table if exists table2;
create table table1
(
   id int not null auto_increment,
   name varchar(10),
   primary key (id)
);
create table table2 like table1;
insert into table1 (name) values ('A'),('B'),('C');
insert into table2 (name) values ('C'),('D'),('E');
SELECT name FROM
(
   SELECT name FROM table1
   UNION
   SELECT name FROM table2
) A;

這是該範常式式碼的執行：

mysql> drop table if exists table1;
Query OK, 0 rows affected (0.03 sec)

mysql> drop table if exists table2;
Query OK, 0 rows affected (0.03 sec)

mysql> create table table1 (
   ->     id int not null auto_increment,
   ->     name varchar(10),
   ->     primary key (id)
   -> );
Query OK, 0 rows affected (0.05 sec)

mysql> create table table2 like table1;
Query OK, 0 rows affected (0.06 sec)

mysql> insert into table1 (name) values ('A'),('B'),('C');
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into table2 (name) values ('C'),('D'),('E');
Query OK, 3 rows affected (0.11 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT name FROM (SELECT name FROM table1
   -> UNION SELECT name FROM table2) A;
+------+
| name |
+------+
| A    |
| B    |
| C    |
| D    |
| E    |
+------+
5 rows in set (0.00 sec)

mysql>

試一試 ！！！

引用自：https://dba.stackexchange.com/questions/11367