[MySQL] 공부 (IN, EXISTS)

자료

https://github.com/nomemory/hr-schema-mysql

GitHub - nomemory/hr-schema-mysql: DML and DDL scripts to generate the HR SQL Schema for MySQL

IN

BETWEEN A AND B 조건은 일정한 범위의 값을 비교하는데 사용한다.

IN은 범위 값과 다르게 여러 개의 값을 동시에 비교해야 할 경우에 사용한다.

-- 사원들 중에서 구매부서(30), it부서(60), 경영부서(90)에 속한 사원들의 정보를 추출하자.
select employee_id 사원번호,
	   concat(first_neme, ' ', last_name) 이름,
	   salary 월급,
	   department_id 부서코드
from employees
where department_id = 30
or department_id = 60
or department_id = 90; -- 14_Row

select employee_id 사원번호,
	   concat(first_neme, ' ', last_name) 이름,
	   salary 월급,
	   department_id 부서코드
from employees
where department_id in(30, 60, 90); -- 14_Row

-- IN 연산자는 OR 연산자와 동일한 기능을 수행한다고 할 수 있다.

select count(*) from employees; -- 107row

-- 구매, it, 경영부서 이외의 부서에 속하는 사원들을 조회해보자.
select employee_id 사원번호,
	   concat(first_name, ' ', last_name) 이름,
	   salary 월급,
	   department_id 부서코드
from employees
where department_id not in(30, 60, 90); -- 92row

select employee_id 사원번호,
	   concat(first_name, ' ', last_name) 이름,
	   salary 월급,
	   department_id 부서코드
from employees
where department_id not in(select department_id
		    from departments
		    where department_id in(30, 60, 90)); -- 92row

exists

in은 어떤 값에 포함되는지 여부를 체크하는 것이라면 exists는 특정 컬럼값이 존재하는지 여부를 체크한다.
in은 괄호 안에 비교할 값이 올 수도 있고 서브쿼리가 올 수도 있는 반면에 exists는 오직 서브쿼리만 올 수 있다.

select employee_id 사원번호,
   concat(first_name, ' ', last_name) 이름,
   salary 월급,
   department_id 부서코드
from employees emp
where exists
  ( select dep.department_id
    from departments dep
    where dep.department_id in(30, 60, 90)
    and emp.department_id = dep.department_id );
        
        
select employee_id 사원번호,
   concat(first_name, ' ', last_name) 이름,
   salary 월급,
   department_id 부서코드
from employees emp
where exists
  ( select 1
    from departments dep
    where dep.department_id in(30, 60, 90)
    and emp.department_id = dep.department_id ); --  -- 93_Row

(위 예제에서) 서브쿼리의 select 1

exists는 존재하느냐 존재하지 않느냐의 여부만 체크하기 때문에
서브쿼리의 select절의 리스트에 등장하는 값과는 상관없이
서브쿼리의 결과로 반환되는 row가 있느냐 없느냐만 중요하다.

 

'=' '<>' 연산자는 컬럼값이 null일 경우 연산을 제대로 수행하지 못한다.
not in (92건)
department_id <> 30 and department_id <> 60 and department_id <> 90
not exists (93건)
30, 60, 90 이외의 모든 값을 가진 row를 조회하게 된다.

in, exists 정리

in

• 테이블의 컬럼값이 특정 값들에 속해 있는지 여부를 판단하는 조건식이다.
• in 조건은 or 조건으로 바꾸어 사용할 수 있다.
• 반드시 괄호와 함께 사용되며 괄호안에는 비교할 값의 리스트나 서브쿼리가 올 수 있다.
• in의 반대 개념은 not in이다.
• 컬럼값이 null 이거나 비교하는 값의 리스트 중에 null이 있으면 제대로 비교를 하지 못한다.

exists

• in과 동일한 기능을 수행하지만, 비교연산 방식은 다르다.
• 뒤에 오는 괄호 안에는 오직 서브쿼리만 올 수 있다.
• 서브쿼리의 where절에서 비교할 기준 테이블의 컬럼과 조인을 맺어야 하며, 서브쿼리의 결과로 반환되는 로우가 1개라도 있을 경우 exists 조건을 만족하게 된다.
• 서브쿼리의 select 리스트에는 어떤 유형의 값도 올 수 있다.
• exists의 반대 개념은 not exists이다. 

join

-- 부서의 이름도 출력하자
select first_name, last_name, email, phone_number, hire_date,
       salary, job_id, employees.department_id,
       departments.department_name
from employees, departments
where employees.department_id = departments.department_id;

select emp.first_name, emp.last_name, emp.email, emp.phone_number, emp.hire_date,
       emp.salary, emp.job_id, emp.department_id,
       dep.department_name
from employees emp, departments dep
where emp.department_id = dep.department_id;

-- join을 할 수 있는 테이블의 개수에는 제한이 없다.
-- 직책명도 보여주자.
select emp.first_name, emp.last_name, emp.email, emp.phone_number, emp.hire_date,
       emp.salary, emp.job_id, emp.department_id,
       dep.department_name, job.job_title
from employees emp, departments dep, jobs job
where emp.department_id = dep.department_id
and emp.job_id = job.job_id;

INNER JOIN

• 외부조인 이외의 조인을 일컫기 위해서 내부(INNER)라는 명칭이 붙게 되었다.
• 내부조인은 테이블 간의 공통 컬럼을 사용하여 이 컬럼값이 같은 데이터들을 연결하는 조인방법을 말한다.
• 따라서 내부조인은 조인조건에 의해 조인한 결과의 row 수가
• 조인에 참여한 테이블의 조회조건에 만족하는 ROW 수와 같게된다.

두 개이상의 테이블 조인

• 사원의 직책명과 부서명을 알아내기 위해서 JOIN을 한다.
• BASE TABLE(기준 테이블) : EMPLOYEES
• JOIN 대상이 되는 테이블이 2개 이상일 때는 어떤 순서로 조인조건을 처리해야 하나?
•  보통을 기준 테이블을 중심으로 하나의 테이블과 조인을 처리한 후 그 결과를 가지고 다시 나머지 테이블과의 조인을 하게 된다.

select concat(emp.first_name, "-", emp.last_name), emp.email,
       emp.department_id, dep.department_name,
       emp.job_id, job.job_title
from employees emp, departments dep, jobs job
where emp.department_id = dep.department_id
and emp.job_id = job.job_id;

먼저 employees와 jobs 테이블의 job_id 컬럼값과 같은 데이터를 추려낸 뒤에,
이 결과를 가지고 다시 departments 테이블의 department_id 컬럼값이 같은 데이터를 찾아내어
최종 결과를 보여주게 된다.

/*
DEPARTMENTS 테이블에는 해당부서가 어느 지역에 있는지 위치 정보를 담고 있는 LOCATION_ID라는 컬럼이 있고,
지역에 대한 상세한 정보는 LOCATIONS 테이블에 들어있다.
이 내용을 가지고 사원들의 정보와 소속된 부서가 어느 도시에 있는지까지 추가하여 알아내자.
*/
select * from employees;
select * from departments;
select * from JOBS;
select * FROM LOCATIONS;

select concat(emp.first_name, "-", emp.last_name), emp.email,
       emp.department_id, dep.department_name,
       emp.job_id, job.job_title, loc.city
from   employees emp, departments dep, jobs job, locations loc
where  emp.department_id = dep.department_id
and    emp.job_id   = job.job_id
and    dep.location_id   = loc.location_id;

두 개 이상의 테이블과 조인할 경우 어떤 테이블과의 조인이 먼저 처리될까?
어떤 식으로 쿼리를 처리할 지는 전적으로 DB엔진의 몫이다.

/* California에 근무하는 사원들의 정보를 출력해보자!
모든 사원들은 부서에 소속이 되어있고 departmants 테이블에는 부서의 위치를 가리키는 location_id가 있다.
위치정보를 관리하는 locations 테이블에는 주(state) 정보를 가지고 있는 state_province 컬럼이 있으므로
이 컬럼에 조건을 걸어주면 된다.
*/
select concat(emp.first_name, "-", emp.last_name), emp.email,
       emp.department_id, dep.department_name,
       emp.job_id, job.job_title, loc.city, loc.state_province
from   employees emp, departments dep, jobs job, locations loc
where  emp.department_id  = dep.department_id
and    emp.job_id    = job.job_id
and    dep.location_id    = loc.location_id
and    loc.state_province = 'California';

select concat(first_name, "-", last_name) 이름, emp.email,
       dep.department_name, job.job_title,
       loc.city, loc.state_province
from   employees emp, departments dep, jobs job, locations loc
where  emp.department_id  = dep.department_id
and    emp.job_id    = job.job_id
and    dep.location_id    = loc.location_id
and    loc.state_province = 'California';

이 문장의 조건 처리 순서
1. locations 테이블에서 state_provice 값이 캘리포니아인 row를 찾는다.
2. 찾은 location_id 값과 같은 값을 가진 데이터를 departments 테이블에서 찾아 조인을 한다.
3. 2의 결과의 employees 테이블을 비교하여 (department_id 값이 같은 것)조인한다.
4. 3의 결과와 jobs 테이블을 비교하여 조인하여 최종 쿼리 결과를 얻는다.
이 문장의 경우 조인에서 사용된 기준 테이블은 locations가 된다.
즉, locations 테이블에서 state_provice 값이 캘리포니아인 로우 수와 전체 쿼리 결과의 로우 수가 같게 된다는 의미이다.

 

지금까지 연습한 문장들의 조인조건은 모두'=' 연산자를 사용했다.
즉, 하나의 테이블에 있는 공통 컬럼의 값이 조인에 참여하는
   또 다른 테이블의 공통 컬럼값과 같은 데이터를 찾는 조건이었다.
이렇게 조인조건에서 등호 연산자(equal operator)를 사용하는 조인을 동등조인(Equi Join)이라고 하며,
대부분의 조인이 이에 속한다.
조인조건에서 '=' 이외의 연산자가 사용된 조인을 비동등 조인(Non-Equi Join)이라고 한다.

 

WHERE 절에 조인조건을 넣지 않으면?
Cartesian Product

- 모든 가능한 행들의 JOIN

- 아래와 같은 경우에 카테시안 곱이 발생한다.
    1. 조인 조건이 생략된 경우
    2. 조인 조건이 잘못된 경우
    3. 첫번째 테이블의 모든 행이 두번째 테이블의 모든 행과 조인이 되는 경우
    - 결과 : 양쪽의 row의 개수를 곱한 개수가 나타난다.
    - Cartesian Product를 방지하기 위해서는 WHERE절에 올바른 JOIN조건을 기술해야 한다.

select e.employee_id, e.first_name, e.email,
       d.department_name
from employees e, departments d; -- 2889row

select count(*) from employees; -- 107_Row
select count(*) from departments; -- 27_Row

107 x 27 = 2889
조인의 핵심은 조인조건이며 조인을 한다는 것은 테이블간의 데이터를 연결한다는 것인데,
조인조건을 빠트린다는 것은 엄밀하게 이야기하면 조인이라고 할 수 없다.
하지만 이러한 경우 DB는 오류를 발생시키지 않고 가능한 모든 조합을 계산하여 결과를 보여준다.

SELF JOIN

셀프조인(SELF JOIN)이라는 말 그대로 자기 자신과 조인을 맺는 것을 말한다.
즉, 실제 조인에 사용되는 테이블은 한 개이지만 FROM절에서는 동일한 테이블을 두 번 명시하고
WHERE절에 조인 조건은 주어 서로 다른 두 개의 테이블을 조인하는 것처럼 사용하게 된다.

 

셀프조인이 아닌 다른 조인의 경우 별칭을 사용하지 않더라도
SELECT 리스트나 WHERE 조건에서 사용되는 컬럼만 정확히 명시하면 되지만,
셀프조인에서는 동일한 테이블을 사용하기 때문에 반드시 별칭(Ailas)를 사용해야 한다.

select e1.employee_id, e2.first_name, e2.last_name
from employees e1, employees e2
where e1.employee_id = e2.employee_id;

셀프조인이 아니더라도 쿼리문의 가독성을 위해서나 SQL문장 작성시 용이함을 위해 항상 별칭을 사용하는 습관을
갖는 것이 좋다.

select emp.employee_id, emp.first_name, emp.department_id,
       dep.department_id, dep.department_name
from employees emp, departments dep
where emp.department_id = dep.department_id;

-- 각 사원별로 매니저가 누구인지 조회하기. (사원아이디, 사원이름, 매니저아이디, 매니저이름)
select * from employees;
select * from departments;

select emp.employee_id 아이디,
       concat(emp.first_name, ' ', emp.last_name) 사원이름,
       emp.manager_id 매니저아이디,
       concat(man.first_name, ' ', man.last_name) 매니저이름
from employees emp, employees man
where emp.manager_id = man.employee_id; -- 106_row

-- 위의 조회 결과는 106건이나, 실제 사원 수는 107명이다.
-- 조회 결과에서 나타나지 않은 1명을 찾기. => Steven King이 왜 106명의 명단에 나타나지 않았나?
select employee_id 아이디, first_name, last_name
from employees
where manager_id is null; -- 매니저가 없는 Steven King

지금까지 공부한 JOIN은 조인조건에 모두 '=' 연산자를 사용한 동등조인(equi join)이었다.
사실 조인이라는 의미 자체가 테이블 간의 공통 컬럼값을 통해 연결한다는 것이므로
조인이라 하면 대부분 동등조인을 말한다.
하지만 동등조건과 달리 '=' 연산자를 사용하지 않는 조인들도 존재한다.

ANTI JOIN

안티 조인이란 말 그대로 동등조인과 반대되는 개념의 조인을 말한다.
안티조인은 조인조건에서 사용되는 연산자를 중심으로 비교대상이 될 좌측의 ROW들과 우측으로 추출될 ROW들이
서로 다른 것을 말한다.
이 말은 연산자가 같지 않다는, 즉 '='이 아닌 연산자를 사용한다는 것이다.

select emp.employee_id, emp.first_name,
       emp.department_id, dep.department_name
from employees emp, departments dep
where emp.department_id = dep.department_id; -- 106_ROW

select emp.employee_id, emp.first_name,
       emp.department_id, dep.department_name
from employees emp, departments dep
where emp.department_id <> dep.department_id; -- 2756_ROW

안티조인은 조인조건에 '!=' 나 '<>' 연산자를 사용하는 것이 아니다.
안티조인은 '!=' 나 '<>' 가 아니라 NOT IN 연산자를 사용하는 조인을 말한다.

select emp.employee_id, emp.first_name
from employees emp
where emp.department_id not in
( select dep.department_id
  from departments dep
  where dep.location_id = 3200 ); 
  -- 107_ROW, 3200(봄베이) 봄베이에서 근무하는 직원이 한명도 없다.

조인이라는 것이 테이블 간의 관계를 맺는 것이며, 그 관계가 꼭 동등한 관계일 필요는 없다.
안티조인에서 테이블 간의 관계는 기준 테이블을 중심으로
기존 테이블의 공통 컬럼의 값과 다른 값을 가진 또 다른 테이블의 데이터를 추출하는 관계인 것이다.
따라서 안티조인이 동등조인과는 반대의 개념이기는 하지만 조인조건에서 사용되는 연산자가 반대인 것은 아니다.

SEMI JOIN

세미조인은 바로 IN 대신에 EXISTS를 사용하는 조인을 말한다.

-- 월 급여가 10,000달러 이상인 사원이 속한 부서의 정보를 추출하자.
select dep.*
from departments dep
where exists ( select 1
               from employees emp
               where emp.department_id = dep.department_id
               and emp.salary > 10000 )
order by dep.department_id; -- 6_Row

OUTER JOIN

외부조인은 일반적인 조인인 내부조인을 확장한 개념의 조인이라고 볼 수 있다.
두 테이블이 내부조인으로 연결되었을 경우 조인의 결과는 조인조건에서 명시된 컬럼값이 두 테이블에 
모든 값은 값을 가진 Row들만 결과로 추출된다.
하지만 외부조인을 하게되면 어느 한 테이블의 공통 컬럼값이 없더라도(컬럼값이 NULL인 경우)
해당 Row들이 조회결과에 포함된다.

select emp.employee_id, concat(emp.first_name, ' ', emp.last_name) name,
       emp.department_id, dep.department_name
from employees emp, departments dep
where emp.department_id = dep.department_id; -- 106_Row

외부조인은 조인에 참여하는 테이블 중 어느 한 테이블에서만
조회조건을 만족한다면 다른 한 테이블의 값이 없더라도 데이터를 조회할 수 있다.

select * from job_history;

오라클에서는 이렇게 한다.

select e.employee_id, e.first_name, e.hire_date,
       j.start_date, j.end_date, j.job_id, j.department_id
from employees e, job_history j
where e.employee_id = j.employee_id -- 오라클에서는 더 적은 쪽에 + / e.employee_id = j.employee_id(+)
order by j.employee_id;

MySQL에서 OUTER JOIN

-- OUTER JOIN/ LEFT OUTER JOIN, RIGHT OUTER JOIN / 기준에서 LEFT,RIGHT 잘 쓸것.
-- 부서가 없는 Kimberely Grant도 나타난다.
SELECT emp.employee_id, CONCAT(emp.first_name, ' ', emp.last_name),
   emp.department_id, dep.department_name
FROM employees emp
 LEFT OUTER JOIN departments dep
     ON emp.department_id = dep.department_id
ORDER BY emp.department_id; -- 107_Row

-- 도시명(location.city)도 보여주세요.
SELECT emp.employee_id, CONCAT(emp.first_name, ' ', emp.last_name),
   emp.department_id, 
       dep.department_name,
       loc.city
FROM employees emp
 LEFT OUTER JOIN departments dep
     ON emp.department_id = dep.department_id,
     locations loc
WHERE dep.location_id = loc.location_id
ORDER BY emp.department_id; -- 106_Row (Kimberely Grant 사라짐)
-- 178번 Kimberely Grant
SELECT emp.employee_id, CONCAT(emp.first_name, ' ', emp.last_name),
   emp.department_id, 
       dep.department_name,
       loc.city
FROM employees emp
 LEFT OUTER JOIN departments dep
     ON emp.department_id = dep.department_id
     LEFT OUTER JOIN locations loc
     ON dep.location_id = loc.location_id
ORDER BY emp.department_id; -- 107_Row

-- JOB_HISTORY : 어떤 사원이 언제부터 언제까지 어떤 직책으로 어떤 부서에 근무했는지의 기록.
SELECT * FROM JOB_HISTORY; -- 10_Row

/*
모든 사원을 조회하는데 직책 변동 기록이 있는 사원이라면 그 변동내역까지 조회하는 쿼리를 작성해 보자.
JOB_HISTORY 테이블에는 일부 사원정보만 있으므로 EMPLOYEES 와 JOB_HISTORY 테이블을 외부조인하면
변동내역이 있는 사원은 그 기록이 나오고, 변동내역이 없는 사원은 일반정보만 조회된다.
*/
-- EMPLOYEES : 사원아이디, 이름, 입사일자
-- JOB_HISTORY : 근무시작일자, 근무종료일자, 직책, 부서
SELECT e.employee_id, e.first_name, e.hire_date,
   j.start_date, j.end_date, j.job_id ,j.department_id, e.department_id
FROM employees e
 LEFT OUTER JOIN job_history j
     ON e.employee_id = j.employee_id
ORDER BY j.employee_id; -- 110_Row /겹치는 사람 3명이어서 107명

/*
이 중에서 현재 부서의 직책 변동 부서가 같은 사원일 경우만 직책변동 내역을 보여주고,
그렇지 않은 경우에는 직책변동이 존재하더라고 그내역을 보여주지 않도록 하자.
*/
SELECT e.employee_id, e.first_name, e.hire_date,
   j.start_date, j.end_date, j.job_id ,j.department_id, e.department_id
FROM employees e
 LEFT OUTER JOIN job_history j
     ON e.employee_id = j.employee_id
WHERE e.department_id = j.department_id
ORDER BY j.employee_id; -- 4_Row

SELECT e.employee_id, e.first_name, e.hire_date,
   j.start_date, j.end_date, j.job_id ,j.department_id, e.department_id
FROM employees e
 LEFT OUTER JOIN job_history j
     ON e.employee_id = j.employee_id
 AND e.department_id = j.department_id
ORDER BY j.employee_id; -- 108_Row

SubQuery

• SubQuery는 하나의 SQL 문장 내부에 존재하는 또 다른 SELECT 문장을 말한다.
• 원래의 SQL 문장을 Main Query라고 한다면,
• SubQuery는 MainQuery 내부에서 추가 정보를 제공할 목적으로 사용되는 SELECT 문장이다.
• SQL 문장 특히 DML(Data Manipulation Language, 데이터 조작어)에 속하는
  SELECT, INSERT, UPDATE, DELETE 등의 모든 문장에서 서브쿼리를 사용할 수 있다.
     
• 서브쿼리는 SELECT 문장에서 SELECT 리스트에 올 수도 있고 FROM 절에 올 수도 있고 WHERE 절에 오는 것도 가능하다.
• 이렇게 어느 곳에 서브쿼리가 위치하는가에 따라서 서브쿼리를 분류할 수 있는데
  FROM 절에 오는 서브쿼리를 인라인 뷰(Inline View)라고 하고, WHERE 절에 오는 서브쿼리를 중첩쿼리(Nested Query)라고 한다.

-- 전체 사원들 중 평균 급여보다 낮은 급여를 받는 사원들의 명단을 추출하자.
-- 평균 월급을 구하는 SQL 문장 1개, 평균 월급보다 낮은 월급을 받는 사원을 추출하는 SQL 문장 1개가 필요하다.
select round(avg(SALARY))
FROM employees; -- 6462 (ROUND 소수점 없애기)

select employee_id, first_name, last_name, salary
FROM employees
where salary < 6462;

select employee_id, first_name, last_name, salary
FROM employees
where salary < ('평균월급을 구해오자');

select employee_id, first_name, last_name, salary
FROM employees
where salary < (select round(avg(SALARY)) from employees); -- 56_Row

두 개의 SELECT 문은 모두 EMPLOYEES 테이브렝서 정보를 추출하지만,
두 QUERY 사이에는 데이터의 연관성이 없이 각각 독립적으로 수행되었다.
연관성 없는 서브쿼리(Noncorrelated Subquery) :
메인쿼리와 서브쿼리 사이에 데이터의 연관성이 없는 서브쿼리.
연관성 있는 서브쿼리(Correlated Subquery) :
메인쿼리와 서브쿼리 사이에 연관성, 즉 서로 데이터의 참조나 공유가 발생하는 경우도 있는 서브쿼리.

 

메인쿼리와의 연관성에 따라서
- 연관성 없는 서브쿼리(Noncorrelated Subquery)
- 연관성 있는 서브쿼리(Correlated Subquery)
    
서브쿼리가 위치하는 곳에 따라서
- 일반 서브쿼리
    - 인라인 뷰(Inline View) : FROM 절에 위치
    - 중첩 쿼리(Nested Query) : WHERE 절에 위치

/* 연관성 없는 서브쿼리(Noncorrelated Subquery) *************************************** */
-- 부서정보를 출력하자
-- 단, 주(location.state_province)가 없는 도시에 위치한 부서정보를 조회해보자.

select city, state_province from locations order by city, state_province;

select *
from  departments
where location_id in ('주(location.state_province)가 없는 도시에 위치한 부서정보');

select location_id from locations where state_province is null;

select *
from  departments
where location_id in (select location_id from locations where state_province is null);

select employee_id, first_name, last_name, salary
FROM employees
where salary < (select round(avg(SALARY)) from employees); -- 56_Row

1. 단일 로우, 단일 컬럼을 반환하는 서브쿼리
   다중 로우, 단일 컬럼을 반환하는 서브쿼리
   다중 컬럼을 반환하는 서브쿼리

1. 단일 로우, 단일 컬럼을 반환하는 서브쿼리
이러한 유형은 대부분이 집계함수가 포함된 쿼리가 많다.

-- 월급을 가장 많이 받는 사원의 이름, 직책명을 알아보자.
select * from employees;
select concat(e.first_name, ' ', e.last_name) 이름, j.job_title 직책명
from employees e, jobs j
where e.salary = (select max(salary) from employees)
AND e.job_id = j.job_id;

-- 월급을 가장 적게 받는 사원의 이름, 직책명을 알아보자.
select * from employees;
select concat(e.first_name, ' ', e.last_name) 이름, j.job_title 직책
from employees e, jobs j
where e.salary = (select min(salary) from employees)
AND e.job_id = j.job_id;

-- 미국 내에서 근무하는 사원들의 평균 월급보다 많은 월급을 받는 사원들의 명단을 조회하자.
-- 미국 내에서 근무하는 사원들 location.country_id = 'US'
select * from locations;

select concat(e.first_name, ' ', e.last_name) 이름, j.job_title 직책
from employees e, jobs j
where e.salary > ( select avg(salary)
   from employees emp,
departments dep,
                        locations loc
   where emp.department_id = dep.department_id
                   and dep.location_id = loc.location_id
   and loc.country_id = 'US'
                  )
AND e.job_id = j.job_id; -- 58_Row