首先准备了两个表 (Student 和 Course),其中 Student 表中的 C_S_Id 字段为外键列,关联的是 Course 表的 C_Id 主键列。
内连接(inner join):满足on条件表达式,内连接是取满足条件表达式的两个表的交集(即两个表都有的数据)。
select * from Student s inner join Course c on s.C_S_Id=c.C_Id
**外连接(outer join)**分为:左外连接(left join / left outer join)、右外连接(right join / right outer join)和全外连接(full join / full outer join)
左外连接(left join / left outer join): 满足on条件表达式,左外连接是以左表为准,返回左表所有的数据,与右表匹配的则有值,没有匹配的则以空(null)取代。
select * from Student s
left join Course c on s.C_S_Id=c.C_Id
右外连接(right join / right outer join):满足on条件表达式,右外连接是以右表为准,返回右表所有的数据,与左表匹配的则有值,没有匹配的则以空(null)取代
select * from Student s
right join Course c on s.C_S_Id=c.C_Id
全外连接(full join / full outer join):满足on条件表达式,返回两个表符合条件的所有行,a表没有匹配的则a表的列返回null,b表没有匹配的则b表的列返回null,即返回的是左连接和右连接的并集。
select * from Student s
full join Course c on s.C_S_Id=c.C_Id
交叉连接(cross join): 交叉连接将会返回被连接的两个表的笛卡尔积,返回结果的行数等于两个表行数的乘积。
不加条件返回两个表行数的乘积:
select * from Student s
cross join Course c
加上条件返回满足条件表达式的两个表的行:
select * from Student s
cross join Course c
where s.C_S_Id=c.C_Id
PS:cross join后加条件只能用where,不能用on,这一点跟后面的自连接一样。
交叉连接有两种,显式的和隐式的,不带ON子句,返回的是两表的乘积,也叫笛卡尔积。
例如:下面的语句1和语句2的结果是相同的。
语句1:隐式的交叉连接,没有CROSS JOIN。
SELECT O.ID, O.ORDER_NUMBER, C.ID, C.NAME
FROM ORDERS O , CUSTOMERS C
WHERE O.ID=1;
语句2:显式的交叉连接,使用CROSS JOIN。
SELECT O.ID,O.ORDER_NUMBER,C.ID,
C.NAME
FROM ORDERS O CROSS JOIN CUSTOMERS C
WHERE O.ID=1;
自连接:自连接,连接的两个表都是同一个表,即自己连接自己。
-- 查询出男生身高比女生身高矮的学生信息
select s1.S_Name,s1.S_Sex,s1.S_Height,s2.S_Name,s2.S_Sex,s2.S_Height
from Student s1,
Student s2
where s1.S_BirthDate=s2.S_BirthDate
and s1.S_Height<s2.S_Height
and s1.S_Sex='男'
and s2.S_Sex='女'
--查询出学生身高一样但是学号不一样的学生信息
select * from Student s1,Student s2
where s1.S_Height=s2.S_Height
and s1.S_StuNo<>s2.S_StuNo
由于表的数据的原因,所以自连接的示例可能不太容易理解。可以看下面这个示例,根据表名查询出表的主外键约束名,示例如下:
select a.Name as 表名,b.Xtype as 键类型,b.Name as 键名
from sysobjects a,sysobjects b
where a.ID=b.parent_obj and a.name='Student'
and b.Xtype in('F','PK')
内连接如果没有指定连接条件的话,和笛卡尔积的交叉连接结果一样,但是不同于笛卡尔积的地方是,没有笛卡尔积那么复杂要先生成行数乘积的数据表,内连接的效率要高于笛卡尔积的交叉连接。
最后说明一下,交叉连接如果有WHERE子句的话,往往会先生成两个表行数乘积的行的数据表然后再根据WHERE条件从中选择。
因此,如果两个表数据量太大,将会非常非常慢,不建议使用。
再看一下sql中主要关键字的执行顺序:
from
on
join
where
group by
having
select
distinct
union
order by
我们看到on是在join和where前面的
如果两张表的数据量都比较大的话,那样就会占用很大的内存空间这显然是不合理的。所以,我们在进行表连接查询的时候一般都会使用JOIN xxx ON xxx的语法,ON语句的执行是在JOIN语句之前的,也就是说两张表数据行之间进行匹配的时候,会先判断数据行是否符合ON语句后面的条件,再决定是否JOIN。
因此,有一个显而易见的SQL优化的方案是,当两张表的数据量比较大,又需要连接查询时,应该使用 FROM table1 JOIN table2 ON xxx的语法,避免使用 FROM table1,table2 WHERE xxx 的语法 ,因为后者会在内存中先生成一张数据量比较大的笛卡尔积表,增加了内存的开销。