9 mysql 서브쿼리 부속질의

부속 질의어는 in 연산자 다음에 사용할 수도 있고, 관계 연산자 다음에도 사용할 수도 있다.

수강신청을을 한 학생의 학번과 이름을 출력하라.

"

select stu_no, stu_name 

from student 

where stu_no in (select stu_no from attend where att_div = 'Y');

"

"=" 연산자는 부속 질의어가 항상 정확히 하나의 값만을 반환할 때 유효하다

김유미 1983년생 보다 나이가 더 많은 각 학생의 학번과 이름, 주민등록번호를 출력하라.

"

select stu_no, stu_name, id_num 

from student

where birth_year  < (select birth_year from student where stu_name = '김유미')

"

부속질의어를 사용하는 3번째 방법은 all과 any 연산자를 사용하는 것이다.

이러한 연산자는 부속 질의어에서 in 연산자를 사용하는 것과 유사하다.

그리고 any 연산자 대신에 some 연산자를 사용할 수 있는데, any 연산자와 some 연산자는 동의어다.

"ALL 연산자의 사용

• ALL 연산자는 IN 연산자와는 달리 어떤 특정한 값이 아닌 범위로 비교연산을 처리한다.

• ALL 연산자는 서브쿼리에서 리턴되는 모든 값을 만족하면 조건이 성립된다.

가장 나이가 많은 학생의 학번, 이름, 출생년도를 출력하라

(단, 가장 나이가 많은 학생은 다른 학생의 출생년도보다 출생년도가 더 적거나 같은 출생년도를 가진 학생이다.)

"select stu_no, stu_name, birth_year 

from student 

where birth_year <= all (select birth_year from student);"

"select stu_no, stu_name, birth_year 

from student 

where birth_year in (select min(birth_year) from student)

"

부속 질의어의 중간 결과는 모든 학생의 생년으로 구성되어 있다.

select 명령문에서 sql은 각 학생의 생년월일이 중간 결과에 기록되어 있는 각 생년월일 보다  작거나 동일한가 보게 된다.

in 연산자를 사용하여 이러한 조건이 참인지 거짓인지 아니면 알 수 없음인지 명확하게 보여 줄 수 있으며, 또 all 연산자를 사용해서도 보여줄 수 있다.

"ANY 연산자의 사용

• ANY 연산자는 IN 연산자와 달리 어떤 특정한 값이 아닌 범위로 비교연산을 처리한다.

• ANY 연산자는 서브쿼리에서 리턴되는 어떠한 값이라도 만족을 하면 조건이 성립된다.

"

any 연산자는 all 연산자의 반대가 되는 연산자이다.

가장 나이가 많은 학생을 제외한 나머지 모든 학생의 학번, 이름, 주민등록번호를 출력하라.

"select stu_no, stu_name, birth_year 

from student 

where birth_year > any (select birth_year from student);"

이거는 왜 다를까...

"

select stu_no, stu_name, birth_year 

from student 

where birth_year not in (select max(birth_year) from student)

;"

학번 20001015인 학생이 등록한 등록금의 납부총액보다 더 많은 등록금을 낸 학생의 학번을 출력하라.

이때 20001015번은 결과에서 제외 한다.

음..

"

select distinct stu_no 

from fee 

where stu_no <> '20001015' 

and fee_pay > any 

(select fee_pay from fee where stu_no = '20001015')

"

"EXISTS 연산자의 사용

• 검색된 결과가 하나라도 존재하면 메인쿼리 조건절이 참이다.

• NOT EXISTS는 서브쿼리에서 검색된 결과가 하나도 존재하지 않으면 메인쿼리 조건절은 참이다.

등록을을 한 학생의 학번과 이름을 출력하라

"select stu_no, stu_name 

from  student 

where stu_no in 

(select stu_no from fee);

"

exists를 사용해서 출력

등록하지 않은 학생의 학번과 이름을 출력하라

"select stu_no, stu_name

from student

where exists 

(select * from fee where stu_no = student.stu_no)"

조건에서 열 명세 student.stu_no은 주 질의어의 명령문에서 사용했던 테이블을 참조하게 된다.

이와 같은 이유는 상호 관련된 부속 질의어를 호출하기 때문이다.

즉, 지정된 열 명세를 사용함으로써 부속 질의어와 주 질의어간에 관계성을 확립한다.

정확하게 이명령문의 의미는 무엇인가? sql은 student 테이블에 있는 모든 학생에 대하여 부속 질의어의 결과로서 행을 반환하는지 반환하지 않는지 결정한다.

다시말하면, where exists 의 결과가 있는지 조사한다는 것이다.

만약 fee 테이블이 학생과 관련된 동일한 학번인 행이 적어도 하나 이상 있다면 그 행은 조건을 만족하게 된다

부정조건

java 길라잡이 동아리에 가입한 학생의 학번과 이름을 출력하라.

select stu_no, stu_name from circle where cir_name = 'Java길라잡이';

java 길라잡이 동아리에 가입 하지 않은 학생의 학번과 이름을 출력하라.

select stu_no, stu_name from circle where cir_name <> 'Java길라잡이';

조건 앞에 not 연산자를 위치시킴으로써 부정 조건을 가지는 select 명령문을 만들 수 있다.

select stu_no, stu_name from circle where not cir_name =  'Java길라잡이';

등록테이블에서 장학코드가 11이 아닌 학생의 학번과 장학코드, 장학금 총액을 출력하라

select stu_no, jang_code, jang_total from fee where jang_code <> 11;

fee 테이블에서 장학코드가 11이거나 null 값을 가지고 있는 학생은 제외되었으므로 원하는 데이터가 출력되지 않은 것을 알 수가 있다.

등록테이블에서 장학코드가 11이 아닌 학생의 학번과 장학코드, 장학금 총액을 출력하라

select stu_no, jang_code, jang_total from fee where jang_code not in (select jang_code from fee where jang_code in (11));

등록테이블에서 장학코드가 11이 아닌 학생의 학번과 장학코드, 장학금 총액을 출력하라

단 not in 이용하고 장학코드가 null 인 학생도 포함하여 출력하라.

"

select stu_no, jang_code, jang_total 

from fee 

where jang_code not in (select jang_code from fee where jang_code in (11)) 

or jang_code is null;"