Oracle连接查询

淩亂°似流年 2021-11-19 11:32 367阅读 0赞

## 左外连接 ##

> 左外连接：左表不加限制，保留左表的数据，匹配右表，右表没有匹配到的行中的列显示为null

### STUDENT ###

SELECT *
    FROM  STUDENT s

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RoZWJpZ2RpcHBlcmJkeA_size_16_color_FFFFFF_t_70]

### COURSE ###

SELECT *
    FROM COURSE c

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RoZWJpZ2RpcHBlcmJkeA_size_16_color_FFFFFF_t_70 1]

### STUDENT s LEFT JOIN COURSE c ###

SELECT * FROM STUDENT s LEFT JOIN COURSE c ON s.S_ID=c.COURSE_STUDENT_FK
    ORDER BY s.S_ID

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RoZWJpZ2RpcHBlcmJkeA_size_16_color_FFFFFF_t_70 2]

## STUDENT s LEFT JOIN COURSE c ON s.S\_ID=c.COURSE\_STUDENT\_FK AND c.c\_name=‘课程四’ ##

SELECT * FROM STUDENT s LEFT JOIN COURSE c ON s.S_ID=c.COURSE_STUDENT_FK AND c.c_name='课程四'
    ORDER BY s.S_ID

![在这里插入图片描述][20190729155149203.png]

## WHERE 1=1 AND c.c\_name=‘课程四’ ##

SELECT * FROM STUDENT s LEFT JOIN COURSE c ON s.S_ID=c.COURSE_STUDENT_FK
    WHERE 1=1 AND c.c_name='课程四'
    ORDER BY s.S_ID

![在这里插入图片描述][20190729155355717.png]

## 右外连接 ##

> 右外连接：右表不加限制，保留右表的数据。匹配左表，左表没有匹配到的行中列显示为null

### STUDENT s RIGHT JOIN COURSE c ###

SELECT * FROM STUDENT s RIGHT JOIN COURSE c ON s.S_ID=c.COURSE_STUDENT_FK
    ORDER BY c.C_ID

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RoZWJpZ2RpcHBlcmJkeA_size_16_color_FFFFFF_t_70 3]

## 内连接 ##

> 取左表与右表的交集（左右表完全匹配）

### STUDENT s INNER JOIN COURSE c ###

SELECT * FROM STUDENT s INNER JOIN COURSE c ON s.S_ID=c.COURSE_STUDENT_FK
    ORDER BY s.S_ID

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RoZWJpZ2RpcHBlcmJkeA_size_16_color_FFFFFF_t_70 4]

## 全连接 ##

> 取左表与右表的并集（左表不加限制，保留左表的数据，并且，右表不加限制，保留右表的数据）

### STUDENT s FULL JOIN COURSE c ###

SELECT * FROM STUDENT s FULL JOIN COURSE c ON s.S_ID=c.COURSE_STUDENT_FK
    ORDER BY s.S_ID

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RoZWJpZ2RpcHBlcmJkeA_size_16_color_FFFFFF_t_70 5]

## SQL脚本 ##

-- STUDENT表
    create table STUDENT
    (
      S_ID   INTEGER not null,
      S_NAME VARCHAR2(20)
    )
    
    alter table STUDENT
      add primary key (S_ID);
      
    insert into STUDENT (S_ID, S_NAME)
    values (1, '天明');
    insert into STUDENT (S_ID, S_NAME)
    values (2, '少羽');
    insert into STUDENT (S_ID, S_NAME)
    values (3, '高月');
    insert into STUDENT (S_ID, S_NAME)
    values (4, '雪女');
    
    -- COURSE表
    create table COURSE
    (
      C_ID              INTEGER not null,
      C_NAME            VARCHAR2(20),
      COURSE_STUDENT_FK INTEGER
    )
    
    alter table COURSE
      add primary key (C_ID);
      
    insert into COURSE (C_ID, C_NAME, COURSE_STUDENT_FK)
    values (1, '课程一', 1);
    insert into COURSE (C_ID, C_NAME, COURSE_STUDENT_FK)
    values (2, '课程一', 2);
    insert into COURSE (C_ID, C_NAME, COURSE_STUDENT_FK)
    values (3, '课程一', 3);
    insert into COURSE (C_ID, C_NAME, COURSE_STUDENT_FK)
    values (4, '课程二', 1);
    insert into COURSE (C_ID, C_NAME, COURSE_STUDENT_FK)
    values (5, '课程二', 3);
    insert into COURSE (C_ID, C_NAME, COURSE_STUDENT_FK)
    values (6, '课程三', 1);
    insert into COURSE (C_ID, C_NAME, COURSE_STUDENT_FK)
    values (7, '课程四', 2);
    insert into COURSE (C_ID, C_NAME, COURSE_STUDENT_FK)
    values (8, '课程十', 10);

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RoZWJpZ2RpcHBlcmJkeA_size_16_color_FFFFFF_t_70]: /images/20211119/2355bc379c034da19d942933d653ecd7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RoZWJpZ2RpcHBlcmJkeA_size_16_color_FFFFFF_t_70 1]: /images/20211119/0392cb579e2c4bd3a65f902eb8740f03.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RoZWJpZ2RpcHBlcmJkeA_size_16_color_FFFFFF_t_70 2]: /images/20211119/8fbc601b71a04f1c8757e71f5870a552.png
[20190729155149203.png]: /images/20211119/d92f70c7b91c47a88be7b9b6eaa059ab.png
[20190729155355717.png]: /images/20211119/829399803de446899a9627f826c74d98.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RoZWJpZ2RpcHBlcmJkeA_size_16_color_FFFFFF_t_70 3]: /images/20211119/1fcea4ee4bc5434fad10ef2b088d6e63.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RoZWJpZ2RpcHBlcmJkeA_size_16_color_FFFFFF_t_70 4]: /images/20211119/b10231ab3c9b458c95e5d61afdef28b1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RoZWJpZ2RpcHBlcmJkeA_size_16_color_FFFFFF_t_70 5]: /images/20211119/d3391c18b24f4aa4b48012508b298184.png