[SQL] 기본 명령어02

[논리연산자]

  SELECT * 
    FROM employees
   WHERE employee_id <> 105 ;
   
  SELECT * 
    FROM employees
   WHERE employee_id > 105 or employee_id < 105 ;
   
  SELECT * 
    FROM employees
   WHERE employee_id not in 105 ;
   
  SELECT * 
    FROM employees
   WHERE employee_id != 105 ;

 

 

[동등조인]

  SELECT e.first_name, e.last_name, d.department_name
    FROM employees e, departments d
   where d.department_id = e.department_id;
   
  SELECT *
    FROM employees e, departments d
  where e.employee_id = 100;

[inner join (조인 되어 있는 테이블안, 같은 컬럼에서 조건 걸기)]

  SELECT e.first_name, e.last_name, d.department_name
    FROM employees e inner join departments d on d.department_id = e.department_id 
    inner join locations l on l.location_id = d.location_id;
  
  SELECT e.first_name, e.last_name, d.department_name, l.city
    FROM employees e, departments d, locations l
   WHERE d.location_id = l.location_id and e.department_id = d.department_id;

left join / right join

  SELECT *
    FROM departments d left join employees e ON d.department_id = e.department_id;
    
   SELECT *
    FROM departments d left outer join employees e ON d.department_id = e.department_id;
    
   SELECT *
    FROM departments d right join employees e ON d.department_id = e.department_id;
    
   SELECT *
    FROM departments d right outer join employees e ON d.department_id = e.department_id;

[left join / right join]

    --오라클
  SELECT *
    FROM employees a, departments b
   where a.department_id(+) = b.department_id;