연산자

1. IN = 리스트 값과 동일하다.

2. NOT IN = 리스트 값과 동일 하지 않다. (is not null 필수)

3. >ALL = 리스트에서 가장 큰 값보다 크다

4. >any = 리스트에서 가장 작은값 보다 크다

5. <all = 리스트에서 가장 작은 값보다 작다

6. <any = 리스트에서 가장 큰값보다 작다

where 절을 쓸때에는 컬럼이 가공 되지 않도록 하는게 좋다 (나중에 index 할때도 컬럼이 가공되면 INdex 적용이 되지않음)

ex )substr(ename), sal*12

  ename like 를 쓰거나, 부등호를 넘겨서 /12 로 하면 좋다.

IN (서브쿼리가 여러개의 값인 경우 )

예제

select ename , sal, job
from emp 
where SAL in (select sal 
              from emp 
              where job = 'SALESMAN');

여러개의 값이 서브쿼리에서 메인쿼리로 리턴되는 경우 IN 을 사용해야 한다.

직업이 salesman 인 사원들의 월급 이기때문에 여러개의 값이라고 말할 수 있다.

문제 : 30번 부서번호인 사원들과 직업이 같은 사원들의 이름과 월급과 직업을 출력

select ename, sal, job 
from emp
where job in (select job 
              from emp 
              where deptno = 30);

서브 쿼리문인 부서번호가 30 번인 직업을 가진 사람들은 여러명이기 때문에 다중행 서브쿼리이다.

따라서 = 가 아닌 연산자 in 을 사용한다.

NOT IN

not in 을 사용할때 sub query 에서 중요한 issue 가 존재한다.

예제

문제 : 30 번 부서번호인 사원들과 직업이 같지 않은 사원들의 이름, 월급 직업 부서번호 를 emp 에서 출력 하세요

select ename, sal, job, deptno 
from emp 
where job not in (select job
                  from emp 
                  where deptno = 30);

문제 : king 에게 보고하는 사원들의 이름을 출력

select ename
from emp
where mgr = (select empno
             from emp
             where ename = 'KING');

문제 : 자기밑에 직속부하가 한명이라도 있는 사원들의 이름을 출력

selec ename
from emp
where empno in (select mgr 
                from emp);

where 절 의미 : 사원번호가 mgr (관리자번호) 에 in 있는 사람들

mgr (관리자번호) 가 사원번호 (empno) 인 사원들인 6명이 바로 관리자인 사원들 즉 자기밑에 직속 부하가 한명이라도 있는 사원들입니다.

mgr 이 null 이 있는데 in 을 사용해도 결과값이 나온 이유?
select ename from emp where empno = 7839 or empno =7566 .... 나머지는 일단 생략 or empno = null;
여기서 =any 의 뜻은 or 로 연결이 되어있다.
select ename from emp where empno =any (select mgr from emp);
—> 생각해보면 or 는 true or true or true ....or null—> 왜? NULL 이 알수없는 값인데, 알 순 없지만 만약 True or true = true.
는 true 이다.
True or false = true 이기때문에 null 값이 true 이던, false 이던 or 한 값은 true 가 나온다.

문제 : 관리자가 아닌 사원들의 이름을 출력하시오 (즉 자기밑에 직속부하가 없는 사원들 ) - 관리자인 사원들이 6명이므로 관리자가 아닌 사원들은 8명이 출력되어야한다.

select ename 
from emp
where empno not in (select mgr 
                    from emp );

—>결과값이 나오지 않는다.

이유는 mgr 에 null 값이 존재하기 때문

king 이 president 여서 mgr (관리자)가 존재하지않는다.

이럴때 not in 을쓰게 되면 값이 출력 되지 않는다.

위의 쿼리를 다른식으로 이야기 해보자면

select ename 
from emp 
where empno !=all (select mgr 
                   from emp);

≠all 의 의미는 and 가 포함되어있다.

select ename 
from emp 
where empno !=7566 and empno !=7839 and ......and empno != null;

이식은

null 은 은 연산자 비교가 안되기때문에 null 값으로 출력된다.

그래서 and null 이 되서 출력 안됨.

true and true ane true and ....and null = null 이다.

and 가 아무리 하더라도 중간에 null 값이 있으면 그냥 전체 다 null 이 되어버린다.

and 값이 제대로 출력 되려면 모두가 true 여야 값이 true 출력된다.

제대로 not in 의 값이 출력되는 SQL 식! mgr 을 null 처리 해주어야한다.

select ename 
from emp 
where empno not in (select mgr 
                    from emp 
                    where mgr is not null );

NULL이 retrne 되지 않도록 is not null 을 쓰면된다.

select ename 
from emp 
where empno not in (select nvl(mgr, -1) ——————> 이것도 mgr 이가공된것이 아니다. 
                    from emp);

아니면 nvl 함수를 사용해도 된다. (nvl (mgr, 0 ) 도 됨,

—>그냥 null 값을 치환할때 해당 컬럼에 없을만한 번호로 치환해 주면 된다.

문제 355 우리반 테이블에서 통신사 출력하는데 telecom_service 의 있는 통신사만 출력

select telecom 
from emp14
where telecom in (select telecom
                  from telecom_service 
                  where telecom is not null );

위의 쿼리의 결과값은 나오지 않는다 —> 정말로 데이터가 없기 때문에 안나온다.

not in 을 사용하여 sub sqery 문을 사용할때 조금이라도 성능을 높이려면 where 절에 null 이 리턴되지 않게하는 조건을 꼭 넣어준다.

문제 341 직업이 세일즈맨인 사원들중에서 최대월급보다 더많은 월급을 받는 사원들의 이름과 월급을 출력

select ename, sal 
from emp
where sal> (select max(sal) —————→ 아래의 sql 과 결과 동일
            from emp                성능이 더 낫다. (단일행이여서) 
            where job = 'SALESMAN');

또는 다중행 연산자를 사용하여 서브쿼리문에 max 를 사용하지 않고 결과 값을 낼 수 있다.

select ename, sal 
from emp
where sal >all (select sal ——————> 위의 sql 결과와 동일 
                from emp
                where job = 'SALESMAN');

>all 은 서브쿼리문의 모든 값보다 크다 라는 의미이다.

서브쿼리문의 세일즈맨들의 월급은 1250, 1600,1500,1250 의 결과값들이 나오는데 이 모든 값보다 크다는 것은 가장 큰 값인 1600 보다 크기만 하면 만족 된다는 것이다.

>any 는 출력된 모든 값들 중 작은 값보다 크기만 하면 된다.

문제 342

select ename, sal
from emp 
where sal > (select min(sal) 
             from emp 
             where to_char(hiredate, 'RRRR') = '1981');

이식도 출력은 되지만 where 절에 hiredate 가공하게 되면 빅데이터시 성능이 굉장히 느려진다.

따라서

hiredate을 고공을 하지 않은 아래의 sql 식이 훨 씬 좋다.

select ename, sal 
from emp 
where sal > (select min(sal) 
             from emp 
             where hiredate between to_date('1981/01/01','RRRR/MM/DD') 
                                and to_date ('1981/12/31', 'RRRR/MM/DD')+ 1);

문제 343 성이 김씨인 학생들과 나이가 같은 학생들의 이름과 나이를 출력 하고 성이 김씨인 학생들은 제외하고 출력하시오

select ename, age 
from emp14 
where age in (select age            ——————————→ 더 좋은 식 
               from emp14 
               where ename like '김%')
 and ename not like '김%';

또는

select ename, age
from emp14
where age in (select age 
              from emp14 
              where substr(ename,1,1)='김')
and substr(ename,1,1)!='김';

두번째 SQL 식은 where 절에서 ename 이 substr 로 가공되었기 때문에 별로 좋지 않은 쿼리문이다.

첫번째 SQL 식이 좋다. !!!!!!

문제 344 연봉이 2600 이상인 사원들과 직업이 같은 사원들의 이름과 직업을 출력하시오

select ename, job
from emp
where job in (select job       ————> where 절이 가공되었기 때문에 
              from emp               안좋은식
              where sal*12 >= 26000);

select ename, job
from emp
where job in (select job ————————> 좋은식 
              from emp 
              where sal >= 26000/12);

where 절의 sal 이 가공되지 않기 위해서 식을 넘겨주었다.

따라서sal *12 가 부등호로 넘어가서 26000/12 로 계산한다

order by 절까지 함께 쓰는 sub query

문제 345 제임스보다 더 많은 월급을 받는 사원들의 이름,월급, 입사일을 출력 하는데 최근에 입사한 사원부터 출력

select ename, sal, hiredate
from emp 
where sal > (select sal 
             from emp 
             where ename = 'JAMES') 
order by hiredate desc;

데이터 베이스 생성

도로교통공단_교통사고다발지역 데이터 데이터 임포트 테이블 명 : car_acc_loc

사고지역위치명, 발생건수, 순위 출력 (순위는 발생건수가 높은순)

select 사고지역위치명, 발생건수, rank() over (order by 발생건수 desc) as 순위 
from car_acc_loc;

문제 : 사고유형구분을 중복제거해서 출력

select distinct(사고유형구분)
from car_acc_loc;

문제 : 사고유형구분, 사고지역위치명, 발생건수, 순위 출력 (순위가 사고유형구분별로 각각 발생건수가 높은순으로 순위를 부여)

select 사고유형구분, 사고지역위치명, 발생건수 , rank() over (partition by 사고유형구분
                                                    order by 발생건수 desc) as 순위
 from car_acc_loc;

이렇게 결과문을 보게 되면 데이터가 많기때문에 무단횡단의 출력물 먼저 쭉나오고 나머지 데이터를 보려면 너무 많이 스크롤을 내려서 보아야 한다.

sql 을 추가해서 작성해 주면

문제 위의 결과에서 1위인 것들만 출력

select * 
from ( select 사고유형구분, 사고지역위치명, 발생건수 , 
              rank() over (partition by 사고유형구분
                           order by 발생건수 desc) as 순위 
         from car_acc_loc )
 where 순위 =1;

문제 351 점심시간문제 : 위의결과를 다시 출력하는데 1위부터, 5위까지 각각 출력

select *
from ( select 사고유형구분, 사고지역위치명, 발생건수 , 
              dense_rank() over (partition by 사고유형구분 
                                 order by 발생건수 desc) as 순위 
      from car_acc_loc ) 
where 순위 <=5;

'Oracle > SQL' 카테고리의 다른 글

SQL Sub Query Exists / Not Exists 존재여부 (0)	2021.11.28
SQL Sub Query 다중컬럼 서브쿼리 (0)	2021.11.28
SQL Sub Query 단일행 where 절 (0)	2021.11.28
SQL 집합연산자 Minus 차집합 (0)	2021.11.28
SQL 집합연산자 Intersect 교집합 (0)	2021.11.28

LOO 의 분석공부

SQL Sub Query 다중행 (IN, NOT IN)

IN (서브쿼리가 여러개의 값인 경우 )

예제

문제 : 30번 부서번호인 사원들과 직업이 같은 사원들의 이름과 월급과 직업을 출력

NOT IN

예제

문제 : 30 번 부서번호인 사원들과 직업이 같지 않은 사원들의 이름, 월급 직업 부서번호 를 emp 에서 출력 하세요

문제 : king 에게 보고하는 사원들의 이름을 출력

문제 : 자기밑에 직속부하가 한명이라도 있는 사원들의 이름을 출력

문제 : 관리자가 아닌 사원들의 이름을 출력하시오 (즉 자기밑에 직속부하가 없는 사원들 ) - 관리자인 사원들이 6명이므로 관리자가 아닌 사원들은 8명이 출력되어야한다.

제대로 not in 의 값이 출력되는 SQL 식! mgr 을 null 처리 해주어야한다.

문제 355 우리반 테이블에서 통신사 출력하는데 telecom_service 의 있는 통신사만 출력

문제 341 직업이 세일즈맨인 사원들중에서 최대월급보다 더많은 월급을 받는 사원들의 이름과 월급을 출력

문제 342

문제 343 성이 김씨인 학생들과 나이가 같은 학생들의 이름과 나이를 출력 하고 성이 김씨인 학생들은 제외하고 출력하시오

문제 344 연봉이 2600 이상인 사원들과 직업이 같은 사원들의 이름과 직업을 출력하시오

order by 절까지 함께 쓰는 sub query

문제 345 제임스보다 더 많은 월급을 받는 사원들의 이름,월급, 입사일을 출력 하는데 최근에 입사한 사원부터 출력

데이터 베이스 생성

사고지역위치명, 발생건수, 순위 출력 (순위는 발생건수가 높은순)

문제 : 사고유형구분을 중복제거해서 출력

문제 : 사고유형구분, 사고지역위치명, 발생건수, 순위 출력 (순위가 사고유형구분별로 각각 발생건수가 높은순으로 순위를 부여)

문제 위의 결과에서 1위인 것들만 출력

문제 351 점심시간문제 : 위의결과를 다시 출력하는데 1위부터, 5위까지 각각 출력

'Oracle > SQL' 카테고리의 다른 글

티스토리툴바

SQL Sub Query 다중행 (IN, NOT IN)

IN (서브쿼리가 여러개의 값인 경우 )

예제

문제 : 30번 부서번호인 사원들과 직업이 같은 사원들의 이름과 월급과 직업을 출력

NOT IN

예제

문제 : 30 번 부서번호인 사원들과 직업이 같지 않은 사원들의 이름, 월급 직업 부서번호 를 emp 에서 출력 하세요

문제 : king 에게 보고하는 사원들의 이름을 출력

문제 : 자기밑에 직속부하가 한명이라도 있는 사원들의 이름을 출력

문제 : 관리자가 아닌 사원들의 이름을 출력하시오 (즉 자기밑에 직속부하가 없는 사원들 ) - 관리자인 사원들이 6명이므로 관리자가 아닌 사원들은 8명이 출력되어야한다.

제대로 not in 의 값이 출력되는 SQL 식! mgr 을 null 처리 해주어야한다.

문제 355 우리반 테이블에서 통신사 출력하는데 telecom_service 의 있는 통신사만 출력

문제 341 직업이 세일즈맨인 사원들중에서 최대월급보다 더많은 월급을 받는 사원들의 이름과 월급을 출력

문제 342

문제 343 성이 김씨인 학생들과 나이가 같은 학생들의 이름과 나이를 출력 하고 성이 김씨인 학생들은 제외하고 출력하시오

문제 344 연봉이 2600 이상인 사원들과 직업이 같은 사원들의 이름과 직업을 출력하시오

order by 절까지 함께 쓰는 sub query

문제 345 제임스보다 더 많은 월급을 받는 사원들의 이름,월급, 입사일을 출력 하는데 최근에 입사한 사원부터 출력

데이터 베이스 생성

사고지역위치명, 발생건수, 순위 출력 (순위는 발생건수가 높은순)

문제 : 사고유형구분을 중복제거해서 출력

문제 : 사고유형구분, 사고지역위치명, 발생건수, 순위 출력 (순위가 사고유형구분별로 각각 발생건수가 높은순으로 순위를 부여)

문제 위의 결과에서 1위인 것들만 출력

문제 351 점심시간문제 : 위의결과를 다시 출력하는데 1위부터, 5위까지 각각 출력

'Oracle > SQL' 카테고리의 다른 글

'Oracle/SQL' Related Articles

티스토리툴바