left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录
right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录
inner join(等值连接) 只返回两个表中联结字段相等的行
full join 返回两张表中的行 left join+right join
UNION在进行表链接后会筛选掉重复的记录
UNION ALL操作只是简单的将两个结果合并后就返回
create table test(
sid number primary key not null,
sname varchar2(200),
scode varchar2(200)
);
commit;
create table test_bak(
sid number primary key not null,
sname varchar2(200),
scode varchar2(200)
);
commit;
BEGIN
FOR I IN 1 .. 4 LOOP
INSERT INTO test VALUES (I,'jason','1000'+i);
END LOOP;
END;
BEGIN
FOR I IN 3 .. 5 LOOP
INSERT INTO test_bak VALUES (I,'jason','1000'+i);
END LOOP;
END;
select * from test;
sid sname scode
1 1 jason 1001
2 2 jason 1002
3 3 jason 1003
4 4 jason 1004
select * from test_bak;
sid sname scode
1 3 jason 1003
2 4 jason 1004
3 5 jason 1005
select * from test union select * from test_bak;--UNION在进行表链接后会筛选掉重复的记录
sid sname scode
1 1 jason 1001
2 2 jason 1002
3 3 jason 1003
4 4 jason 1004
5 5 jason 1005
select * from test union all select * from test_bak;--UNION ALL操作只是简单的将两个结果合并后就返回
sid sname scode
1 1 jason 1001
2 2 jason 1002
3 3 jason 1003
4 4 jason 1004
5 3 jason 1003
6 4 jason 1004
7 5 jason 1005
select test.*,test_bak.* from test inner join test_bak on test.sid = test_bak.sid ;--只返回两个表中联结字段相等的行
1 3 jason 1003 3 jason 1003
2 4 jason 1004 4 jason 1004
select test.*,test_bak.* from test left join test_bak on test.sid = test_bak.sid ;--返回包括左表中的所有记录和右表中联结字段相等的记录
1 1 jason 1001 null null null
2 2 jason 1002 null null null
3 3 jason 1003 3 jason 1003
4 4 jason 1004 4 jason 1004
select test.*,test_bak.* from test right join test_bak on test.sid = test_bak.sid ;--返回包括右表中的所有记录和左表中联结字段相等的记录
1 3 jason 1003 3 jason 1003
2 4 jason 1004 4 jason 1004
3 null null null 5 jason 1005
select test.*,test_bak.* from test full join test_bak on test.sid = test_bak.sid ;--返回左右两表中的所有记录,相同的记录会同行显示
1 1 jason 1001 null null null
2 2 jason 1002 null null null
3 3 jason 1003 3 jason 1003
4 4 jason 1004 4 jason 1004
5 null null null 5 jason 1005
分享到:
相关推荐
SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL Union SQL Select Into SQL Create DB SQL Create Table SQL Constraints SQL Not Null SQL Unique SQL Primary Key SQL Foreign Key ...
Within so many kinds of DML statements in SQL, I think four kinds of them, including Case When, Left Outer Join/Left Join / Right Outer Join /Right Join/ Inner Join, WITH AS and UNION/UNION ALL, are ...
内连接(inner join)和外连接(left join/right join/full join) 排序(Order By) 条件(Where) 分组(Group By) 分组条件(Having) 计算字段 SQL查询表 SQL查询子句 丰富的函数 表别名 字段别名 联合(Union...
3.7.1 SQL 92 标准:Inner Join、Left Join、Right Join、Full Join, 3.7.2 特殊语法:*=、=*、*=*(MS_SQL,Sybase),(+)(Oracle) 3.8 联合 (Union [All],Minus,Intersect) 3.9 字段别名,数据表别名 ...
3.7.1 SQL 92 标准:Inner Join、Left Join、Right Join、Full Join, 3.7.2 特殊语法:*=、=*、*=*(MS_SQL,Sybase),(+)(Oracle) 3.8 联合 (Union [All],Minus,Intersect) 3.9 字段别名,数据表别名 ...
SQL中表等值连接(内连接) inner join SQL中表左连接(左外连接) left join SQL中表右连接(右外连接) right join SQL中表交叉连接(两张表的乘积) SQL中表全连接 full join SQL中变量 SQL中的事务 创建存储过程 存储...
select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where ..... 12、说明:日程安排提前五分钟提醒 SQL: select * from 日程安排 where datediff('minute',f...
select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where ..... 12、说明:日程安排提前五分钟提醒 SQL: select * from 日程安排 where datediff('minute',f...
select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where ..... 12、说明:日程安排提前五分钟提醒 SQL: select * from 日程安排 where datediff('minute',f...
SQL语法大全 SQL语法大全 1. ASP与Access数据库连接: dim conn,mdbfile mdbfile=server.mappath("数据库名称.mdb") set conn=server.createobject("adodb.connection") conn.open "driver={microsoft access ...
select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where ..... 12、说明:日程安排提前五分钟提醒 SQL: select * from 日程安排 where datediff('minute',f...
select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where ..... 12、说明:日程安排提前五分钟提醒 SQL: select * from 日程安排 where datediff('minute',f...
实例参考如下: SQL的Join语法有很多, inner join(等值连接) 只返回两个表中联结字段相等的⾏, left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录, right join(右联接) 返回包括右表中的...
二者有很大区别 “%”可以代表任意长度的字符串,长度可以为0; “_”只能表示单个字符。 如果要匹配姓张且名字只有两个字的人的记录,“张”字后面必须要有两个“_”符号。因为一个汉字是两个字符,而一个“_”符号...
A set of transactions can run concurrently if their outputs are disjoint from the union of one another’s input and output sets. For example, if T1 writes some object that is in T2’s input or output ...
主要包括select, update, insert, alter, index, delete, all其中all包括所有权限。 授予实体权限 用法:grant 实体权限1[,实体权限2]… on 表名 to用户名1[,用户名2]…. 例子: 实体权限回收 用法:revoke ...