至少在oracle中，您应该能够做到：

SELECT 
  person.name, job.name, job.status 
FROM 
  person
LEFT JOIN 
   job ON person.id=job.personId AND job.status = 'current';

那什么时候 person.id=job.person.id AND job.status = 'current' 连接被建立，当这个条件为false时，你只得到 person 数据。
我不知道mysql是否支持 ON 但我想是的，这并不是什么突破性的技术。
更新：
看起来它是受支持的，请阅读https://dev.mysql.com/doc/refman/8.0/en/join.html

试试这个

SELECT 
  person.name, job.name, job.status 
FROM 
  person
LEFT JOIN 
  job ON person.id=job.person.id
WHERE CASE WHEN job.status IS NULL THEN '@' WHEN job.status ='current' THEN '@' else job.status END = '@'

Select p.name, j.name,j.status 
     From person p 
Left Join
         (Select name,status,personId 
            From job 
            Where status ='current'
         ) j On p.id = j.personId;

你能试试这个查询吗？

有几种方法可以做到这一点；最佳选择取决于您的数据库以及不同方法的执行方式。
方法一：用或左连接

SELECT p.name, j.name, j.status
FROM person AS p
LEFT JOIN job AS j ON j.personId = p.id AND j.status = 'current';
-- Returns all persons, and only jobs w/ value 'current'
-- If the filter on j.status were in the WHERE clause, it would remove non-matching

方法2：使用union all的两个查询（这将删除没有当前VAL或空VAL的行）

SELECT p.name, j.name, j.status
FROM person AS p
INNER JOIN job AS j ON j.personId = p.id
WHERE j.status = 'current'
OR j.status IS NULL

UNION ALL

SELECT p.name, j.name, j.status
FROM person AS p
LEFT JOIN job AS j ON j.personId = p.id
WHERE j.status IS NULL;

方法2b：使用union all的两个查询（这将保留没有当前VAL或空VAL的行）

SELECT p.name, j.name, j.status
FROM person AS p
INNER JOIN job AS j ON j.personId = p.id
WHERE j.status = 'current'
OR j.status IS NULL

UNION ALL

SELECT p.name, j.name, j.status
FROM person AS p
LEFT JOIN job AS j ON j.personId = p.id
WHERE NOT EXISTS 
  (SELECT 1 FROM jobs AS t WHERE t.personId=p.Id AND t.status='current')
GROUP BY p.Id; 
-- GROUP BY ensures we only get one row back per person, but will be random if there are multiples

方法3：子选择

SELECT p.name, j.name, j.status
FROM person AS p
LEFT JOIN (
  SELECT x.name
  FROM job AS x
  WHERE x.status = 'current'
) AS j;