多表连接查询和子查询
select s.* ,teacher_name
from student_table s,teacher_table t,
where s.java_teacher =t.teacher_id;
select s.* ,teacher_name
from student_table s,teacher_table t,
where s.java_teacher =t.teacher_id and student_name is not null;
外连接(在连接条件的列名后增加括号包括起来的外链接符(+或*))
select s.* ,teacher_name
from student_table s,teacher_table t,
--右外链接
where s.java_teacher =t.teacher_id(*);
SQL99
交叉连接cross join
select s.*,teacher name
from student_table s
cross join teacher_table t;
自然连接 natural join
select s.*,teacher name
from student_table s
natural join teacher_table t;
on子句连接
select s.*,teacher name
from student_table s
join teacher_table t
on s.java_teacher=t.teacher_id;
左右全外连接 left[outer]join right[outer]join full[outer]join
select s.*,teacher name
from student_table s
full join teacher_table t
on s.java_teacher=t.teacher_id;
select s.*,teacher name
from student_table s
left join teacher_table t
on s.java_teacher=t.teacher_id;
select s.*,teacher name
from student_table s
right join teacher_table t
on s.java_teacher=t.teacher_id;
子查询:在查询语句中嵌套另一个查询,子查询支持多层嵌套,
· eg:
select*
from (select * from student_table)t
where t.java_teacher>1;
select*
from student_table
where java_teacher>
(select teacher_id
from teacher_table
where teacher_name='Hello world'
);