关于SQL语句的多表连接查询

附件为mysql、mssqlserver和oracle的建表脚本

# 多表连接查询

# SQL92 
# 笛卡尔积
select * from student_table s, teacher_table t;
# 等值连接 =
select * from student_table s, teacher_table t where s.java_teacher = t.teacher_id;
# 非等值连接 > < 
select * from student_table s, teacher_table t where s.java_teacher > t.teacher_id;
select * from student_table s, teacher_table t where s.java_teacher < t.teacher_id;
# 多条件连接
select * from student_table s, teacher_table t where s.java_teacher = t.teacher_id and s.student_id > 1 and t.teacher_id < 2;
# 外连接
# （MySql和MSSQLServer不支持，使用Oracle测试）左外连接(显示满足连接条件的行，并把右边表不满足条件的行与左边表虚构的所有值为null的行连接起来)
select * from student_table s, teacher_table t where s.java_teacher(+) = t.teacher_id;
# （MySql和MSSQLServer不支持，使用Oracle测试）右外连接(显示满足连接条件的行，并把左边表不满足条件的行与右边表虚构的所有值为null的行连接起来)
select * from student_table s, teacher_table t where s.java_teacher = t.teacher_id(+);

# SQL99
# 交叉连接（SQL92中的笛卡尔积） cross join
select * from student_table s cross join teacher_table t;
# （MSSQLServer不支持，MySql和Oracle支持）自然连接（同名列连接） natural join , 若无同名列则为交叉连接
select * from student_table s natural join teacher_table t;
# （MSSQLServer不支持，MySql和Oracle支持）使用using子句进行指定多个同名列连接（若没有同名属性则报错）
select * from student_table s join teacher_table t using (teacher_name);
# 使用join...on子句进行等值连接 =
select * from student_table s join teacher_table t on s.java_teacher = t.teacher_id;
# 使用join...on子句进行非等值连接 > <
select * from student_table s join teacher_table t on s.java_teacher > t.teacher_id;
select * from student_table s join teacher_table t on s.java_teacher < t.teacher_id;
# 使用join...on子句进行多条件连接
select * from student_table s join teacher_table t on s.java_teacher = t.teacher_id and s.student_id > 1 and t.teacher_id < 2;
# 左外连接 left joinj...on 与SQL92标准相反(显示满足连接条件的行，并把左边表不满足条件的行与右边表虚构的所有值为null的行连接起来)
select * from student_table s left join teacher_table t on s.java_teacher = t.teacher_id;
# 右外连接 right join...on 与SQL92标准相反(显示满足连接条件的行，并把右边表不满足条件的行与左边表虚构的所有值为null的行连接起来)
select * from student_table s right join teacher_table t on s.java_teacher = t.teacher_id;
# （MySql不支持，MSSQLServer和Oracle支持）全外连接 full join...on (显示满足连接条件的行，并把左边表不满足条件的行与右边表虚构的所有值为null的行连接起来，同时把右边表不满足条件的行与左边表虚构的所有值为null的行连接起来)
select * from student_table s full join teacher_table t on s.java_teacher = t.teacher_id;