mysql on和where,连接汇总

感觉sql很垃圾，连接忘完了，整理一下 连接分为left join,right join,和join吧 join就是inner join **

jon 条件放在on或者where后面一样，没啥区别，不过滤 left join和right join 把条件放在on和where后面，有区别

** eg:

CREATE TABLE `product` ( `id` int(10) unsigned NOT NULL auto_increment, `amount` int(10) unsigned default NULL, PRIMARY KEY (`id`) ) CHARSET=latin1 CREATE TABLE `product_details` ( `id` int(10) unsigned NOT NULL, `weight` int(10) unsigned default NULL, `exist` int(10) unsigned default NULL, PRIMARY KEY (`id`) ) CHARSET=latin1 INSERT INTO product (id,amount) VALUES (1,100),(2,200),(3,300),(4,400); INSERT INTO product_details (id,weight,exist) VALUES (2,22,0),(4,44,1),(5,55,0),(6,66,1); //这个是正常的，不用连接得sql，条件放到where后面 select * from product a , product_details b where a.id=b.id and a.id!=2 and b.id!=2; select * from product a inner join product_details b on a.id=b.id and a.id!=2 and b.id!=2; select * from product a inner join product_details b on a.id=b.id where a.id!=2 and b.id!=2;

left join

select * from product_details a left join product b on a.id=b.id; select * from product_details a left join product b on a.id=b.id and a.id=2; select * from product_details a left join product b on a.id=b.id where a.id=2;

right join

select * from product_details a right join product b on a.id=b.id; select * from product_details a right join product b on a.id=b.id and a.id=2; select * from product_details a right join product b on a.id=b.id where a.id=2;

select * from product_details a right join product b on a.id=b.id where a.id!=2;