[sql] 다른 테이블에서 일치하는 항목이없는 행을 선택하는 방법은 무엇입니까?

Program / 글쓴이

데이터베이스 응용 프로그램에서 일부 유지 관리 작업을 수행하고 있으며 한 테이블의 값이 외래 키 스타일로 사용되지만 테이블에 외래 키 제약 조건이 없다는 것을 기쁘게 생각합니다.

이 열에 FK 제약 조건을 추가하려고하지만 순전히 수정 된 이전 오류의 테이블에 이미 잘못된 데이터가 많이 있기 때문에 그렇지 않은 행을 찾아야합니다. 다른 테이블과 일치시킨 다음 삭제하십시오.

웹에서 이러한 종류의 쿼리에 대한 몇 가지 예를 찾았지만 모두 설명이 아닌 예를 제공하는 것처럼 보이며 왜 작동하는지 이해하지 못합니다.

사람이 차라리이의 모든 테이블에 대해 SO에 실행 오는 것보다, 이러한 쿼리 나 자신을 할 수 있도록, 그것은하고있어 사항 다른 테이블에 일치하는 모든 행을 반환하는 쿼리, 무엇을 구성하는 방법을 나에게 설명 할 수 엉망 이 FK 제약이 없습니까?

답변

다음은 간단한 쿼리입니다.

SELECT t1.ID
FROM Table1 t1
    LEFT JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t2.ID IS NULL

핵심 사항은 다음과 같습니다.

  1. LEFT JOIN사용; Table1일치하는 행이 있는지 여부에 관계없이 에서 모든 행을 반환 합니다 Table2.

  2. WHERE t2.ID IS NULL절; 이렇게하면 반환 된 결과가 반환 된 ID Table2가 null 인 행으로 제한됩니다. 즉, 해당 특정 ID 에 대한 레코드 가 없습니다 . 에서 ID가 일치하지 않는 모든 레코드에 대해 NULL로 반환됩니다 .Table2Table1Table2.IDTable1Table2

답변

나는 EXISTS그것이 더 강력하기 때문에 expression 을 사용할 것입니다. 즉, LEFT JOIN결합 된 테이블에있는 모든 것을 가져 와야 할 경우에 더 정확하게 결합 할 행을 선택할 수 있습니다. 효율성은 LEFT JOINnull 테스트의 경우와 동일 합니다.

SELECT t1.ID
FROM Table1 t1
WHERE NOT EXISTS (SELECT t2.ID FROM Table2 t2 WHERE t1.ID = t2.ID)

답변

SELECT id FROM table1 WHERE foreign_key_id_column NOT IN (SELECT id FROM table2)

표 1에는 외래 키 제약 조건을 추가하려는 열이 있지만 표 2 의 값과 foreign_key_id_column모두 일치하지는 않습니다 id.

  1. 초기 선택에는 id 은 table1 s를 . 이들은 삭제하려는 행이됩니다.
  2. NOT IN를 Where 문에 절은의 값은 행에 대한 쿼리 제한 foreign_key_id_column테이블 2의 목록에없는 id들.
  3. SELECT괄호 안의 문장은 모든 목록을 얻을 것이다 id표 2에있는들.

답변

T2구속 조건을 추가 할 테이블은 어디에 있습니까?

SELECT *
FROM T2
WHERE constrained_field NOT
IN (
    SELECT DISTINCT t.constrained_field
    FROM T2
    INNER JOIN T1 t
    USING ( constrained_field )
)

그리고 결과를 삭제하십시오.

답변

다음 2 개의 테이블 (급여 및 직원)을 보자
여기에 이미지 설명을 입력하십시오

이제 급여가 아닌 직원 테이블의 레코드를 원합니다.
우리는 3 가지 방법으로 이것을 할 수 있습니다 :

  1. 내부 조인 사용
select * from employee
where id not in(select e.id from employee e inner join salary s on e.id=s.id)

여기에 이미지 설명을 입력하십시오

  1. 왼쪽 외부 조인 사용 
select * from employee e
left outer join salary s on e.id=s.id  where s.id is null

여기에 이미지 설명을 입력하십시오

  1. 전체 조인 사용
select * from employee e
full outer join salary s on e.id=s.id where e.id not in(select id from salary)

여기에 이미지 설명을 입력하십시오

답변

비슷한 질문에서 MySQL Inner Join Query는 다른 테이블에 레코드를 표시하지 않습니다. 이 작업을 수행했습니다.

SELECT * FROM bigtable
LEFT JOIN smalltable ON bigtable.id = smalltable.id
WHERE smalltable.id IS NULL

smalltable기록이없는 bigtable곳 , 모든 기록이있는 곳입니다. 쿼리에는 존재하지 smalltable않지만에있는 모든 레코드가 나열됩니다 bigtable. id다른 일치 기준으로 대체 할 수 있습니다.

답변

아래와 같이 보기 를 선택할 수 있습니다 .

CREATE VIEW AuthorizedUserProjectView AS select t1.username as username, t1.email as useremail, p.id as projectid,
(select m.role from userproject m where m.projectid = p.id and m.userid = t1.id) as role
FROM authorizeduser as t1, project as p

그런 다음 선택하거나 업데이트하기 위해 뷰에서 작업하십시오.

select * from AuthorizedUserProjectView where projectid = 49

일치하지 않는 열 null이 채워진 경우 아래 그림과 같이 결과가 생성됩니다.

[Result of select on the view][1]