准备表t1和t2
CREATE TABLE `t1` (
`cust_nbr` varchar(20) NOT NULL,
`acc_nbr` varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `t2` (
`cust_nbr` varchar(20) NOT NULL,
`acc_nbr` varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
准备数据:
insert into `t1` (`cust_nbr`, `acc_nbr`) values('a1','ac1');
insert into `t1` (`cust_nbr`, `acc_nbr`) values('a1','ac2');
insert into `t1` (`cust_nbr`, `acc_nbr`) values('a2','ac21');
insert into `t1` (`cust_nbr`, `acc_nbr`) values('a2','ac22');
insert into `t1` (`cust_nbr`, `acc_nbr`) values('a3','ac31');
insert into `t1` (`cust_nbr`, `acc_nbr`) values('a4','ac41');
insert into `t1` (`cust_nbr`, `acc_nbr`) values('a4','ac42');
insert into `t1` (`cust_nbr`, `acc_nbr`) values('a5','ac51');
insert into `t1` (`cust_nbr`, `acc_nbr`) values('a5','ac52');
insert into `t2` (`cust_nbr`, `acc_nbr`) values('a1','ac1');
insert into `t2` (`cust_nbr`, `acc_nbr`) values('a1','ac2');
insert into `t2` (`cust_nbr`, `acc_nbr`) values('a2','ac21');
insert into `t2` (`cust_nbr`, `acc_nbr`) values('a2','ac22');
insert into `t2` (`cust_nbr`, `acc_nbr`) values('a3','ac31');
insert into `t2` (`cust_nbr`, `acc_nbr`) values('a4','ac41');
insert into `t2` (`cust_nbr`, `acc_nbr`) values('a4','ac42');
insert into `t2` (`cust_nbr`, `acc_nbr`) values('a5','ac51');
insert into `t2` (`cust_nbr`, `acc_nbr`) values('a1','ac3');
insert into `t2` (`cust_nbr`, `acc_nbr`) values('a1','ac4');
insert into `t2` (`cust_nbr`, `acc_nbr`) values('a2','ac23');
insert into `t2` (`cust_nbr`, `acc_nbr`) values('a2','ac24');
insert into `t2` (`cust_nbr`, `acc_nbr`) values('a3','ac32');
insert into `t2` (`cust_nbr`, `acc_nbr`) values('a3','ac33');
insert into `t2` (`cust_nbr`, `acc_nbr`) values('a4','ac43');
insert into `t2` (`cust_nbr`, `acc_nbr`) values('a4','ac44');
insert into `t2` (`cust_nbr`, `acc_nbr`) values('a5','ac52');
insert into `t2` (`cust_nbr`, `acc_nbr`) values('a5','ac53');
1.
SELECT * FROM t1 LEFT JOIN t2 ON t1.cust_nbr=t2.cust_nbr
WHERE t1.cust_nbr='a1';
2.
SELECT * FROM t1 LEFT JOIN t2 ON t1.cust_nbr=t2.cust_nbr
AND t1.cust_nbr='a1';
1和2语句是否可以互换!
结论,不可以!
on后面只能跟连接的表相等的字段,如果某个字段跟上常量,用以上的例子来说 就表示t1和t2通过cust_nbr='a1'的字段进行连接,但是其他不等于a1的字段仍然打印出来,只是这些字段右边的t2字段都是null。
结果如下:
如果是where后面加上条件,则表示只打印cust_nbr='a1'的字段,
结果如下