对以下内容的查询是什么？ [英] What will be the query for the following?

问题描述

假设有2个表，员工和工作。 Jobid是员工表中的FK，指的是工作表。在员工中查找违反外键约束的行的合适选项是什么？



（a）

  SELECT  employee.emp_id，employee.job_id  FROM 员工 LEFT   OUTER   JOIN  jobs  ON  employee.job_id<> jobs.job_id 

（b）

  SELECT  employee.emp_id，employee.job_id  FROM 员工 LEFT   OUTER   JOIN  jobs  ON  employee.job_id = jobs.job_id 

（c）

  SELECT  employee.emp_id，employee.job_id  FRO M 员工 LEFT   OUTER   JOIN  jobs  ON  employee.job_id = jobs.job_id  WHERE （jobs.job_id  IS   NULL ）

（d）

  SELECT  employee.emp_id，employee.job_id  FROM  employee  INNER   JOIN  jobs  ON  employee.job_id = jobs.job_id  WHERE （jobs.job_id  IS   NULL ）

解决方案

从员工中选择*

除了

从employee.job_id = jobs.job_id上的员工内部联接职位中选择abc。*

和e mployee.job_id不为空

 选择 employee.emp_id，employee.job_id 
 来自员工
  left  外部  join  jobs   employee.job_id = jobs.job_id 
  where  employee.job_id    null  或 employee.job_id<> jobs.job_id 

Suppose there are 2 tables, employee and jobs. Jobid is a FK in employees table referring the jobs table. What is the suitable option to find the rows in employee that violate foreign key constraints?

(a)

SELECT employee.emp_id,employee.job_id FROM employee LEFT OUTER JOIN jobs ON employee.job_id<>jobs.job_id

(b)

SELECT employee.emp_id,employee.job_id FROM employee LEFT OUTER JOIN jobs ON employee.job_id=jobs.job_id

(c )

SELECT employee.emp_id,employee.job_id FROM employee LEFT OUTER JOIN jobs ON employee.job_id=jobs.job_id WHERE (jobs.job_id IS NULL)

(d)

SELECT employee.emp_id,employee.job_id FROM employee INNER JOIN jobs ON employee.job_id=jobs.job_id WHERE (jobs.job_id IS NULL)

解决方案

select * from employee
except
select abc.* from employee inner join jobs on employee.job_id = jobs.job_id
and employee.job_id is not null

select employee.emp_id, employee.job_id 
from employee 
left outer join jobs on employee.job_id = jobs.job_id
where employee.job_id is null or employee.job_id <> jobs.job_id

这篇关于对以下内容的查询是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！