Oracle DB 연산자
in Programming on SQL
산술연산자
비교연산자
부등호 : =, != , >, <, >=, <=
논리 연산자
AND
SELECT FIRST_NAME, JOB_ID,SALARY FROM EMPLOYEES
WHERE department_id <= 80 AND department_id >=50;
SELECT FIRST_NAME, JOB_ID,SALARY FROM EMPLOYEES
WHERE department_id BETWEEN 50 AND 80
OR
SELECT FIRST_NAME, JOB_ID,SALARY FROM EMPLOYEES
WHERE department_id =80 OR department_id =50;
IN
SELECT FIRST_NAME, JOB_ID,SALARY FROM EMPLOYEES
WHERE department_id IN (50,80);
위 OR과 같은 의미다! 깔끔함.
NOT
SELECT FIRST_NAME, JOB_ID,SALARY FROM EMPLOYEES
WHERE NOT (department_id =80);
LIKE %
SELECT FIRST_NAME, JOB_ID,SALARY FROM EMPLOYEES
WHERE FIRST_NAME LIKE '%n';
%는 0글자 이상 여러글자
SELECT FIRST_NAME, JOB_ID,SALARY FROM EMPLOYEES
WHERE FIRST_NAME LIKE '__n';
_는 한글자
IS NULL / IS NOT NULL
SELECT FIRST_NAME, JOB_ID,SALARY FROM EMPLOYEES
WHERE commision_pct IS NULL;
SELECT FIRST_NAME,SALARY, SALARY*(1+ commission_pct) as "총합" FROM EMPLOYEES
WHERE commission_pct IS NOT NULL;
집합 연산자
UNION , MINUS , INTERSECT
SELECT 로 질의된 결과물을 하나의 집합으로
SELECT FIRST_NAME, SALARY, DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID =80 --세미콜론 없앰
UNION
SELECT FIRST_NAME, SALARY, DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID =50;
UNION ALL 쓰면 중복 데이터가 생략되지 않고 다 나온다.
SELECT FIRST_NAME, SALARY, DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID =80 --세미콜론 없앰
UNION ALL
SELECT FIRST_NAME, SALARY, DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID =50;
MINUS : 차집합. 중복되는 자료들을 빼고 보여준다.
SELECT FIRST_NAME, SALARY, DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID =80 --세미콜론 없앰
MINUS
SELECT FIRST_NAME, SALARY, DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID =50;
INTERSECT : 중복자료들만 보여준다.
SELECT FIRST_NAME, SALARY, DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID =80 --세미콜론 없앰
INTERSECT
SELECT FIRST_NAME, SALARY, DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID =50;
