KADB学习之表连接的使用

KADB 是北京人大金仓信息技术股份有限公司基于开源的greenplum数据库研发的一款分布式关系型数据库，全称是KingbaseAnalyticsDataBase。

学习kadb的表连接

连接测试数据库；

psql postgres

手动执行表连接和子查询测试语句集；

创建测试表和数据

CREATE TABLE COMPANY(
 ID INT PRIMARY KEY   NOT NULL,
 NAME     TEXT  NOT NULL,
 AGE    INT   NOT NULL,
 ADDRESS    CHAR(50),
 SALARY   REAL
);
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul', 32, 'California', 20000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Allen', 25, 'Texas', 15000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'David', 27, 'Texas', 85000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, 'Kim', 22, 'South-Hall', 45000.00 );
INSERT INTO COMPANY VALUES (7, 'James', 24, 'Houston', 10000.00 );
INSERT INTO COMPANY VALUES (8, 'Paul', 24, 'Houston', 20000.00);
INSERT INTO COMPANY VALUES (9, 'James', 44, 'Norway', 5000.00);
INSERT INTO COMPANY VALUES (10, 'James', 45, 'Texas', 5000.00);
CREATE TABLE DEPARTMENT(
   ID INT PRIMARY KEY      NOT NULL,
   DEPT           CHAR(50) NOT NULL,
   EMP_ID         INT      NOT NULL
);
INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (1, 'IT Billing', 1 );
INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (2, 'Engineering', 2 );
INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (3, 'Finance', 7 );

CREATE TABLE emp(
        id SERIAL NOT NULL,
        name varchar(255),
        leader int4,
        age int4,
        gender varchar(255)
    );

insert into emp VALUES (1,'john', null, 30, 'men');
insert into emp VALUES (2,'may', 1, 31, 'women');
insert into emp VALUES (3,'wolf', 2, 29, 'men');
insert into emp VALUES (4,'mar', 1, 40, 'women');
insert into emp VALUES (5,'jack', 4, 28, 'men');
insert into emp VALUES (6,'lili', 4, 37, 'women');

1、内连接

SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;

2、左外连接

SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;

3、右外连接

SELECT EMP_ID, NAME, DEPT FROM COMPANY RIGHT OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;

4、自连接操作

SELECT

    emp1.NAME emp_name,

emp2.NAME leader

FROM

    emp emp1

LEFT JOIN emp emp2 ON emp1.leader = emp2.ID;