SQL语句从两个表中选择

Question

我有两个表employees, salary_advance。

employees 表有列 empid, name, salary 和 salary_advance 有 id, empid, amount, date

我想显示所有员工的姓名、薪水、剩余...

remaining = ( salary - amount ) 

当我进行内部加入时，我只会得到提前参加的员工..

我想显示谁在 employees 中提前 + 其他员工..

这是我的SQL语句

select 
    employees.name , employees.salary , 
    (employees.salary - salary_advance.amount ) 
from 
    employees 
inner join 
    salary_advance on employees.empid = salary_advance.empid 

Answer 1

您需要使用 LEFT OUTER JOIN 而不是 INNER JOIN，并且您还需要使用 ISNULL 来从 salary_advance 表中获取 0 而不是 NULL:

SELECT
    employees.name, 
    employees.salary, 
    Remaining = (employees.salary - ISNULL(salary_advance.amount, 0) ) 
FROM 
    employees 
LEFT OUTER JOIN 
    salary_advance ON employees.empid = salary_advance.empid