表空为MySql连接返回null

I have 2 tables: A and B. I want a query which will return results if any of the values are satisfied in either of the 2 tables. I tried using a join, but it returned null when the second table is empty and vice versa.

我有2个表:A和B.我想要一个查询,如果在2个表中的任何一个表中满足任何值,它将返回结果。我尝试使用连接,但是当第二个表为空时它返回null,反之亦然。

Table A

    emp_no emp_add  data
    12      go      nice

Table B

    emp_no emp_add id 
    12      go      1

Required output

    data   id
    nice   1

Similarly

Table A

    emp_no emp_add  data
    12      go      nice

Table B

    emp_no emp_add id

Required output

    data  id
    nice


SELECT A.data, B.id 
FROM   A left join B ON A.emp_no = B.emp_no 
WHERE  A.data='nice' 
AND    a.id='1' ;

4 个解决方案

#1

Try this::

Select ifnull(a.data,'') as data, ifnull(b.id,'') as id  from tableA a left join tableB b on (a.emp_no=b.emp_no)

#2

use Left Join instead on the table that you accept null values

在您接受空值的表上使用Left Join

#3

As you told that it would happen vice versa, it would better to use Outer Join in your case:

正如你所说,反之亦然,最好在你的情况下使用外连接:

 Select tblA.data,tblB.id from tableA tblA FULL OUTER JOIN tableB tblB on tblA.emp_no=tblB.emp_no

#4

select tableA.data, tableB.id from tableA, tableB

#1