[MYSQL] 중첩 질의문과 부속 질의문, 집합 연산자

부속질의문(Sub-query): 다른 SELECT 문에 포함되어지는 SELECT 문

중첩질의문(Nested-query): 하나 이상의 부속질의문을 포함하는 SELECT 문

 

SELECT *

  FROM (

      SELECT * FROM Person WHERE city='서울'
    ) P

  WHERE name='홍길동';

=> 서울에 사는 사람들 중 이름이 홍길동인 사람 모두 출력

 

에서 붉은색이 중첩질의문, 초록색이 부속질의문이다.

 

너무 용어에 얽메일 필요는 없다.

 

주의할 점은 부속질의문의 결과를

외부에서 사용(참조)하고자 할 때는 반드시 부속질의문에 Alias를 붙여주어야 한다는 점이다

 

SELECT 절의 부속질의문

SELECT city, 
    (SELECT count(*) FROM Person ip 
      WHERE ip.person_number=op.person_number AND ip.gender='남'
    ) AS man_count
  FROM Person op
  WHERE city='서울';

열 이름 자리에 부속질의문이 들어갈 수도 있다.

 

이 쿼리는 서울에 사는 사람들 중 남자의 수를 구하는 쿼리다.

(person_number는 주민등록번호이고 중복되지 않는 고유값이라고 가정)

 

WHERE 절의 부속질의문: 단일 값 비교

SELECT count(*) as cnt
  FROM Person op
  WHERE name=(SELECT name FROM Person ip WHERE ip.person_number='123456-1234567');

WHERE 조건식 우측에 부속질의문이 들어갈 수도 있다.

 

단 이때는 하나의 값에 대해 비교하므로, 부속질의문은 반드시 1X1 테이블이여야 한다.

 

이 쿼리는 주민번호가 123456-1234567인 사람의 이름과 같은 사람의 수를 구하는 쿼리다.

(만약 홍길동의 주민번호가 123456-1234567라면, 사람들 중 홍길동이라는 이름을 가진 사람의 수를 구함)

 

WHERE 절의 부속질의문: 다중 값 비교 (IN)

SELECT *
  FROM Person
  WHERE person_number IN(SELECT person_number FROM Job);

=대신 IN을 사용하면 다중 값에 대한 비교를 할 수 있다.

 

IN은 왼쪽에 들어가는 값이 오른쪽에 있는 1XN 값에 포함되는지를 검사한다.

 

만약 홍길동이라는 사람의 주민번호가 123456-1234567이고 직업이 없을 경우,

결과값에 이 사람은 표시되지 않을 것이다.

 

또한 IN으로 작성한 쿼리문은 조인으로 변환할 수 있다.

SELECT Person.*
  FROM Person JOIN Job 
    ON Person.person_number= Job.person_number;

위의 쿼리문과 같은 결과를 반환한다

 

조인 쿼리에 대해 잘 모르겠다면, 아래 포스트를 읽어보는 것을 추천한다.

 

satisfactoryplace.tistory.com/218

[MYSQL] 조인(JOIN)

WHERE 절의 부속질의문: NOT IN

IN과 반대로,

NOT IN은 왼쪽에 들어가는 값이 오른쪽에 있는 1XN 값에 포함되지 않는지를 검사한다.

SELECT *
  FROM Person
  WHERE person_number NOT IN(SELECT person_number FROM Job);

이 쿼리는 직업이 없는 사람들의 정보를 출력한다.

 

그런데 NOT IN은 JOIN으로 표현하면 상당히 더러워진다.

SELECT Person.*
  FROM Person LEFT OUTER JOIN Job 
    ON Person.person_number= Job.person_number
  WHERE Job.job_id IS NULL;

우욱...

 

지금은 쿼리가 간단해서 그럭저럭 읽을만 하지만, 

NOT IN과 비교해서는 도대체 뭘 원하는 건지 한눈에 알아보기가 어렵다.

WHERE 절의 부속질의문: EXISTS

EXISTS( subquery ) 형식으로 이루어져 있다.

 

IN과 다른 점은 왼쪽에 비교할 열을 명시하지 않으며,

subquery의 결과가 0개면 false, 1개 이상이면 true가 된다는 것이다.

 

SELECT *
  FROM Person p
  WHERE EXISTS(SELECT * FROM Job j WHERE p.person_number = j.person_number);

이렇게 하면 Person테이블의 각 열마다

p.person_number의 값이 Job의 person_number와 일치하는 열이 있는지를 검사하고,

만약 존재하면 true가 되어 해당 열은 출력 결과에 포함된다.

=> 직업을 가진 사람 정보 출력

 

WHERE 절의 부속질의문: NOT EXISTS

NOT EXISTS( subquery ) 형식으로 사용하고,

EXISTS( subquery ) 와 결과가 반대이다.

=> subquery의 열 갯수가 0개면 true, 1개 이상이면 false.

 

SELECT *
  FROM Person p
  WHERE NOT EXISTS(SELECT * FROM Job j WHERE p.person_number = j.person_number);

=> 직업을 가지지 않은 사람 정보 출력

 

집합 연산자: Set Operator

집합 연산자를 이용해 두 개의 부속질의문에 대해 집합연산을 할 수 있다.

 

단, 집합 연산자의 피연산자인 부속질의문의 결과는 서로 합병 가능(union-compatible)해야 한다

=> 합병 가능: 두 테이블의 열 갯수가 같고, 각각의 열의 이름, 도메인이 일치하는 경우

 

(SELECT *
  FROM Person
  WHERE gender='남')
UNION
(SELECT
  FROM Person
  WHERE city='서울')

UNION은 합집합을 의미한다.

 

UNION: 합집합 ( A∪B; 중복 열 없음  )

UNION ALL: 중복을 제거하지 않은 합집합( A∪B의 결과에서 A∩B의 결과가 중복되어 등장; 집합 이론에서는 한 집합 내에서 중복을 허용하지 않지만, 이해를 돕기 위해 집합 기호 사용 )

INSERSECT: 교집합 ( A∩B; A와 B에서 동일하게 나타나는 열만 포함)

 

 

차집합의 경우 NOT IN을 사용해 구현한다.