1.left join(左联接)
sql语句如下:
SELECT * FROM a
LEFT JOIN b
ON a.aID =b.bID
left join是以A表的记录为基础的,A可以看成左表,B可以看成右表,left join是以左表为准的.
换句话说,左表(A)的记录将会全部表示出来,而右表(B)只会显示符合搜索条件的记录(例子中为: A.aID = B.bID).
B表记录不足的地方均为NULL.
2.right join(右联接)
sql语句如下:
SELECT * FROM a
RIGHT JOING b
ON a.aID = b.bID
仔细观察一下,就会发现,和left join的结果刚好相反,这次是以右表(B)为基础的,A表不足的地方用NULL填充.
3.inner join(相等联接或内联接)
sql语句如下:
SELECT * FROM a
INNER JOIN b
ON a.aID =b.bID
等同于以下SQL句:
SELECT *
FROM a,b
WHERE a.aID = b.bID
很明显,这里只显示出了 A.aID = B.bID的记录.这说明inner join并不以谁为基础,它只显示符合条件的记录.
LEFT JOIN操作用于在任何的 FROM 子句中,
$sql = "select n.id,n.name,n.brand,n.dp_name,i.path
from wp_notice n LEFT JOIN wp_notice_img i on n.id=i.id where n.id ".$car_id;
$jplist = M('xydzp_jplist')
->join('left join ' . $fix . 'xydzp_option on ' . $fix . 'xydzp_jplist.xydzp_option_id=' . $fix . 'xydzp_option.id')
->field($fix . "xydzp_option.title,pic," . $fix . "xydzp_option.miaoshu,". $fix . "xydzp_option.isdf")
->where(array('xydzp_id' => $xydzp_id))
->order("type asc,gailv asc")
->select();
sql语句如下:
SELECT * FROM a
LEFT JOIN b
ON a.aID =b.bID
left join是以A表的记录为基础的,A可以看成左表,B可以看成右表,left join是以左表为准的.
换句话说,左表(A)的记录将会全部表示出来,而右表(B)只会显示符合搜索条件的记录(例子中为: A.aID = B.bID).
B表记录不足的地方均为NULL.
2.right join(右联接)
sql语句如下:
SELECT * FROM a
RIGHT JOING b
ON a.aID = b.bID
仔细观察一下,就会发现,和left join的结果刚好相反,这次是以右表(B)为基础的,A表不足的地方用NULL填充.
3.inner join(相等联接或内联接)
sql语句如下:
SELECT * FROM a
INNER JOIN b
ON a.aID =b.bID
等同于以下SQL句:
SELECT *
FROM a,b
WHERE a.aID = b.bID
很明显,这里只显示出了 A.aID = B.bID的记录.这说明inner join并不以谁为基础,它只显示符合条件的记录.
LEFT JOIN操作用于在任何的 FROM 子句中,
$sql = "select n.id,n.name,n.brand,n.dp_name,i.path
from wp_notice n LEFT JOIN wp_notice_img i on n.id=i.id where n.id ".$car_id;
$jplist = M('xydzp_jplist')
->join('left join ' . $fix . 'xydzp_option on ' . $fix . 'xydzp_jplist.xydzp_option_id=' . $fix . 'xydzp_option.id')
->field($fix . "xydzp_option.title,pic," . $fix . "xydzp_option.miaoshu,". $fix . "xydzp_option.isdf")
->where(array('xydzp_id' => $xydzp_id))
->order("type asc,gailv asc")
->select();