select e.EmployeeID,p.FirstName,p.LastName from HumanResources.Employee as e
join Person.Contact as p on e.ContactID = p.ContactID
where e.EmployeeID not in
(select distinct j.EmployeeID from HumanResources.JobCandidate as j where j.EmployeeID is not null)
用的是AdventureWorks表,不用子查询用联结怎么写呢??
join Person.Contact as p on e.ContactID = p.ContactID
join HumanResources.JobCandidate as j on e.EmployeeID= j.EmployeeID and j.EmployeeID is not null
where j.EmployeeID is null
join Person.Contact as p on e.ContactID = p.ContactID
left join HumanResources.JobCandidate as j on e.EmployeeID= j.EmployeeID and j.EmployeeID is not null
where j.EmployeeID is null
这里为什么要用left join呢? left join连接的是Person.Contact as p还是HumanResources.Employee as e啊?还有就是 我把on子句后面的and j.EmployeeID is not null去掉也是正确的。。那这句可以不要?