Oracle连接查询

左外连接

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

STUDENT

SELECT *
FROM  STUDENT s

COURSE

SELECT *
FROM COURSE c

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

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

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

右外连接

右外连接：右表不加限制，保留右表的数据。匹配左表，左表没有匹配到的行中列显示为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

内连接

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

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

全连接

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

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

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);