A 表 left jion B表 on条件 当条件为真,则 B 表对应的行取出
A表 left jion B表 on 条件
这个语句形成的也是一个结果集,可以看成一张表C,可以用条件where,group,having orderby,limit照常使用
可以对这张表C进行查询,C表可以查询的列有哪些?
答:AB的列都可以查
//表goodsinfo内联接表salerecord,条件:'2014-01-02'<saledate<'2014-02-25',且goodsinfo.goods_id = salerecord.goods_id
SELECT goodsinfo.goods_id, goods_name, cat_name, parprice, stocksize, salerecord.saledate, salerecord.price, salerecord.salenumber, salerecord.off, salerecord.total
FROM goodsinfo
INNER JOIN salerecord ON saledate
BETWEEN '2014-01-26'
AND '2014-02-25'
AND goodsinfo.goods_id = salerecord.goods_id
LIMIT 0 , 30
//
//表goodsinfo内联接表salerecord,条件saledate=2014-02-25,,且goodsinfo.goods_id = salerecord.goods_idSELECT goodsinfo.goods_id, goods_name, cat_name, parprice, stocksize, salerecord.saledate, salerecord.price, salerecord.salenumber, salerecord.off, salerecord.total
FROM goodsinfo
INNER JOIN salerecord ON saledate = '2014-02-25'
AND goodsinfo.goods_id = salerecord.goods_id
LIMIT 0 , 30
A表right join B表 on条件 此句为右查询,结果跟left join一样,只是数据的顺序了一样
A表 inner join B表 on条件 此句为内查询,是表A与表B的交集
SELECT supplierinfo.supplier_id, supplierinfo.supplier, supplierinfo.manager, supplierinfo.phone, supplierinfo.address, supplierinfo.memo, goodsinfo.enterdate
FROM goodsinfo INNER JOIN supplierinfo ON goodsinfo.supplier = supplierinfo.supplier
AND enterdate > '2014-01-14'AND enterdate < '2014-03-14'
AND supplierinfo.supplier = '小米'