postgresql join使用
交叉连接即即笛卡尔积
准备数据
CREATE TABLE EMP
( EMPNO int PRIMARY KEY,
ENAME VARCHAR(10),
JOB VARCHAR(9),
MGR int,
HIREDATE DATE,
SAL int,
COMM int,
DEPTNO int
)
insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO) values(7369,'SMITH','CLERK',7902,'17-DEC-80',800,20);
insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values(7499,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30);
insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values(7521,'WARD','SALESMAN',7698,'22-FEB-81',1250,500,30);
insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO) values(7566,'JONES','MANAGER',7839,'02-APR-81',2975,20);
insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values(7654,'MARTIN','SALESMAN',7698,'28-SEP-81',1250,1400,30);
insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO) values(7698,'BLAKE','MANAGER',7839,'01-MAY-81',2850,30);
insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO) values(7782,'CLARK','MANAGER',7839,'09-JUN-81',2450,10);
insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO) values(7788,'SCOTT','ANALYST',7566,'19-APR-87',3000,20);
insert into emp (EMPNO,ENAME,JOB,HIREDATE,SAL,DEPTNO) values(7839,'KING','PRESIDENT','17-NOV-81',5000,10);
insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values(7844,'TURNER','SALESMAN',7698,'08-SEP-81',1500,0,30);
insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO) values(7876,'ADAMS','CLERK',7788,'23-MAY-87',1100,20);
insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO) values(7900,'JAMES','CLERK',7698,'03-DEC-81',950,30);
insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO) values(7902,'FORD','ANALYST',7566,'03-DEC-81',3000,20);
insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO) values(7934,'MILLER','CLERK',7782,'23-JAN-82',1300,10);
CREATE TABLE DEPT
( DEPTNO int PRIMARY KEY,
DNAME VARCHAR(14),
LOC VARCHAR(13)
);
insert into DEPT values(10,'ACCOUNTING','NEW YORK');
insert into DEPT values(20,'RESEARCH','DALLAS');
insert into DEPT values(30,'SALES','CHICAGO');
insert into DEPT values(40,'OPERATIONS','BOSTON');
用法
mydb=# select * from dept;
deptno | dname | loc
--------+------------+----------
10 | ACCOUNTING | NEW YORK
20 | RESEARCH | DALLAS
30 | SALES | CHICAGO
40 | OPERATIONS | BOSTON
(4 rows)
mydb=# select * from emp;
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+--------+-----------+------+------------+------+------+--------
7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | | 20
7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30
7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30
7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | | 20
7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30
7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | | 30
7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | | 10
7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000 | | 20
7839 | KING | PRESIDENT | | 1981-11-17 | 5000 | | 10
7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30
7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100 | | 20
7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | | 30
7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | | 20
7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300 | | 10
(14 rows)
mydb=# select count(*) from emp;
count
-------
14
(1 row)
mydb=# select count(*) from dept;
count
-------
4
(1 row)
mydb=# select count(*) from dept cross join emp;
count
-------
56
(1 row)
mydb=# select count(*) from emp cross join dept; ;
count
-------
56
(1 row)
mydb=#
left outer join
查询以左表为主，左表的每一行去匹配右表，右表中没有匹配到左表的字段则为NULL
mydb=# select * from emp left outer join dept on dept.deptno=emp.deptno;
empno | ename | job | mgr | hiredate | sal | comm | deptno | deptno | dname | loc
-------+--------+-----------+------+------------+------+------+--------+--------+------------+------
----
7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | | 20 | 20 | RESEARCH | DALLA
S
7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 | 30 | SALES | CHICA
GO
7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 | 30 | SALES | CHICA
GO
7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | | 20 | 20 | RESEARCH | DALLA
S
7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 | 30 | SALES | CHICA
GO
7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | | 30 | 30 | SALES | CHICA
GO
7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | | 10 | 10 | ACCOUNTING | NEW Y
ORK
7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000 | | 20 | 20 | RESEARCH | DALLA
S
7839 | KING | PRESIDENT | | 1981-11-17 | 5000 | | 10 | 10 | ACCOUNTING | NEW Y
ORK
7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 | 30 | SALES | CHICA
GO
7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100 | | 20 | 20 | RESEARCH | DALLA
S
7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | | 30 | 30 | SALES | CHICA
GO
7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | | 20 | 20 | RESEARCH | DALLA
S
7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300 | | 10 | 10 | ACCOUNTING | NEW Y
ORK
(14 rows)
right outer join
查询以右表为主，右表的每一行去匹配左表，左表中没有匹配到右表的字段则为NULL
mydb=# select * from emp right outer join dept on emp.deptno=dept.deptno;
empno | ename | job | mgr | hiredate | sal | comm | deptno | deptno | dname | loc
-------+--------+-----------+------+------------+------+------+--------+--------+------------+------
----
7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | | 20 | 20 | RESEARCH | DALLA
S
7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 | 30 | SALES | CHICA
GO
7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 | 30 | SALES | CHICA
GO
7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | | 20 | 20 | RESEARCH | DALLA
S
7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 | 30 | SALES | CHICA
GO
7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | | 30 | 30 | SALES | CHICA
GO
7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | | 10 | 10 | ACCOUNTING | NEW Y
ORK
7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000 | | 20 | 20 | RESEARCH | DALLA
S
7839 | KING | PRESIDENT | | 1981-11-17 | 5000 | | 10 | 10 | ACCOUNTING | NEW Y
ORK
7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 | 30 | SALES | CHICA
GO
7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100 | | 20 | 20 | RESEARCH | DALLA
S
7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | | 30 | 30 | SALES | CHICA
GO
7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | | 20 | 20 | RESEARCH | DALLA
S
7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300 | | 10 | 10 | ACCOUNTING | NEW Y
ORK
| | | | | | | | 40 | OPERATIONS | BOSTO
N
(15 rows)
POSTGRESQL中join 子句中的a join b using（a,b）相当于a join b on a.xx=b.xx
