mysql - 合并两个表的结果

Question

我有两个表

TABLE_A+-------+------------------+------+-----+---------+-------+| Field | Type             | Null | Key | Default | Extra |+-------+------------------+------+-----+---------+-------+| bid   | int(10) unsigned | NO   | PRI | 0       |       || uid   | int(10) unsigned | NO   | PRI | 0       |       |+-------+------------------+------+-----+---------+-------+2 rows in set (0.00 sec)

and

TABLE_B+-------+------------------+------+-----+---------+-------+| Field | Type             | Null | Key | Default | Extra |+-------+------------------+------+-----+---------+-------+| bid   | int(10) unsigned | NO   | PRI | 0       |       || uid   | int(10) unsigned | NO   | PRI | 0       |       |+-------+------------------+------+-----+---------+-------+

I want to select bid from both tables when the uid = 123;Note: each table has about 15 results and some exists in both tables, I need to select distinctively.so I tried this:

SELECT DISTINCT ta.bid, 
                tb.bid 
FROM   table_a AS ta 
       JOIN table_b AS tb using (uid) 
WHERE  uid = 123; 

显然我得到了错误的答案。为什么它会得到 150 多个结果而不是 30 个？

Answer 1

试试这个

SELECT DISTINCT bid FROM TABLE_A WHERE uid = 123
UNION 
SELECT DISTINCT bid FROM TABLE_B WHERE uid = 123

或

SELECT DISTINCT bid 
FROM (SELECT bid FROM TABLE_A WHERE uid = 123
      UNION 
      SELECT bid FROM TABLE_B WHERE uid = 123
     ) AS A