一个查询的理解:
create table ua (
id int not null primary key,
name varchar(90)
);
create table ub (
id int not null primary key,
name varchar(90)
);
insert into LIUJSH.UA (ID, NAME) values (1, 'a1');
insert into LIUJSH.UA (ID, NAME) values (2, 'b1');
insert into LIUJSH.UA (ID, NAME) values (3, 'c1');
insert into LIUJSH.UB (ID, NAME) values (1, 'a');
insert into LIUJSH.UB (ID, NAME) values (2, 'b');
insert into LIUJSH.UB (ID, NAME) values (3, 'c');
select * from ua a left join ub b on a.id=b.id where a.id=1;
select * from ua a left join ub b on a.id=b.id and a.id=1 ;
select * from ua a left join ub b on a.id=b.id and b.id=1;
select * from ua a left join ub b on a.id=1;
select * from ua a left join ub b on 1=1;
以上5个SQL均可以正常查出数据,实验结论:
1. * 可以含有重复的列名
2. on 和 where 中的连接条件(无论是否带括号),都是用作连接条件(同时也可以是过滤条件)
3. where 后的过滤条件,仅是在连接后做的过滤。
4. on a.id=1 仅使用a.id=1做连接ub,(其他ua为外连接)
5. on 1=1 做笛卡尔积