14.비등가조인

NON EQUL JOIN(비등가 조인)

1.NON EQUL JOIN

조인조건이 = 이외인 경우를 비등가 조인이라고 한다,범위를 나타내는 경우

where 절에 >,<,between,등의 조건식을 사용한다

select *
from employees,sal_grade
where salary BETWEEN lowsal and highsal;

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	EMAIL	PHONE_NUMBER	HIRE_DATE	JOB_ID	SALARY	COMMISSION_PCT	MANAGER_ID	DEPARTMENT_ID	GRADE	LOWSAL	HIGHSAL
132	TJ	Olson	TJOLSON	650.124.8234	07/04/10	ST_CLERK	2100		121	50	5	700	2100
128	Steven	Markle	SMARKLE	650.124.1434	08/03/08	ST_CLERK	2200		120	50	4	2101	4000

 

select first_name,salary,grade
from employees,sal_grade
where salary BETWEEN lowsal and highsal;

FIRST_NAME	SALARY	GRADE
Curtis	3100	4
Jean	3100	4
Alana	3100	4
Julia	3200	4
Stephen	3200	4
Winston	3200	4
Samuel	3200	4

 

문제>1 : 사원의 이름과 부서명,급여등급을 출력하세요(내부조인,비등가조인)

select e.first_name, d.department_name, s.grade
from employees e, departments d, sal_grade s
where e.department_id = d.department_id
 and e.salary BETWEEN s.lowsal and s.highsal;

FIRST_NAME	DEPARTMENT_NAME	GRADE
TJ	Shipping	5
Steven	Shipping	4
Hazel	Shipping	4

---

2.SELF JOIN —

자신의 테이블을 두개의 테이블처럼 사용하는것

select e1.employee_id, e1.first_name,    
       e2.employee_id "직속상사 id", 
       e2.first_name "직속상사 이름"
from employees e1, employees e2
where e1.manager_id = e2.employee_id;

EMPLOYEE_ID	FIRST_NAME	직속상사 id	직속상사 이름
173	Sundita	148	Gerald
172	Elizabeth	148	Gerald
171	William	148	Gerald
170	Tayler	148	Gerald