mysql - 如果查询没有对应的值，如何返回空值？

Question

我有一个问题

select c.name as companyname, u.name,u.email,u.role,a.date 
from useraccount u, company c, audittrial a 
where 
    u.status='active' 
    and u.companyid=c.id 
    and 
    (
        u.companyid=a.companyID 
        and a.activity like 'User activated%' 
        and a.email=u.email
    ) 
order by u.companyid desc 
limit 10

所以如果下面的部分不满足，

(
    u.companyid=a.companyID 
    and a.activity like 'User activated%' 
    and a.email=u.email
)

不会返回任何行..

但我想返回以下查询的结果

select c.name as companyname, u.name,u.email,u.role,a.date 
from useraccount u, company c, audittrial a 
where 
    u.status='active' 
    and u.companyid=c.id 
order by u.companyid desc 
limit 10

但要补充一点，如果可用，我应该返回日期，如果日期不可用，则返回空值..

我该怎么做？

Answer 1

多个表结合 where 子句是有效的内连接:

select *  from table1, table2 where table1.id = table2.id

与

相同

select *  from table1 inner join table2 on table1.id = table2.id

要使审计跟踪成为可选的，将其更改为左连接:

select  ...
from    useraccount u
join    company c
on      u.companyid = c.id
left join
        audittrial a
on      a.activity like 'User activated%' 
        and a.email = u.email
order by
        u.companyid desc 
limit   10

顺便说一下，我认为正确的拼写是 audit tr ai l。