等值连接

1
2
3
4

SELECT * FROM EMPLOYEES,DEPARTMENTS
WHERE EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID

等值连接(内连接，不匹配的会被过滤掉，不显示的)简写，可以给每个表明起一个别名

1
2
3
SELECT e.EMPLOYEE_ID,e.DEPARTMENT_ID FROM EMPLOYEES e,DEPARTMENTS D
WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID

等值连接(左外连接，会返回左表不满足条件的)

1
2
3
4
5
6
7
8
SELECT 
  e.EMPLOYEE_ID,e.DEPARTMENT_ID 
FROM 
  EMPLOYEES e,DEPARTMENTS D
WHERE 
  e.DEPARTMENT_ID = d.DEPARTMENT_ID(+)

#//--需要在右边表加上一个(+)

三个表连接查询，需要2个等值判断条件。连接n个表，需要n-1个等值条件

1
2
3
4
5
6
7
8
9
SELECT
  e.EMPLOYEE_ID,
  e.LAST_NAME,
  e.DEPARTMENT_ID,
  d.DEPARTMENT_NAME,
  l.CITY
FROM EMPLOYEES e, DEPARTMENTS D, LOCATIONS l
WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID AND d.LOCATION_ID = l.LOCATION_ID

非等值连接

1
2
3
4
5
6
7
8
9
10
SELECT
  EMPLOYEE_ID,
  LAST_NAME,
  SALARY,
  GRADE_LEVEL
FROM JOB_GRADES, EMPLOYEES e
WHERE e.SALARY BETWEEN LOWEST_SAL AND HIGHEST_SAL and e.EMPLOYEE_ID=132
ORDER BY EMPLOYEE_ID


1
2
3
4
5

SELECT *
FROM DEPARTMENTS JOIN EMPLOYEES
USING(department_id);

左外连接

1
2
3
4
5
SELECT *
FROM EMPLOYEES
  left OUTER JOIN DEPARTMENTS
    ON EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID
#107条记录

右外连接

1
2
3
4
5
6
SELECT *
FROM EMPLOYEES
  RIGHT OUTER JOIN DEPARTMENTS
    ON EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID
#122条记录

满外连接。左表，右边不满足条件的记录都会显示出来

1
2
3
4
5
6
SELECT *
FROM EMPLOYEES
  full OUTER JOIN DEPARTMENTS
    ON EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID
#123条记录

自连接

1
2
3
4
5
6
7
8
9
10
11
SELECT
  emp.LAST_NAME "员工姓名",
  manager.LAST_NAME "老板姓名",
  manager.SALARY,
  manager.EMAIL
FROM EMPLOYEES emp, EMPLOYEES manager
WHERE emp.MANAGER_ID = manager.EMPLOYEE_ID
      AND emp.LAST_NAME = 'Chen'