MySQL LEFT JOIN 分析

NO IMAGE
1 Star2 Stars3 Stars4 Stars5 Stars 給文章打分!
Loading...

1.

select feedback.Id,Title,Problem,feedback.User as feedbackUser,Content,reply.User as replyUser from feedback
left join reply
on feedback.Id = reply.FeedbackId
where feedback.Status=1 and reply.Status=1
order by feedback.CreateDate desc,reply.CreateDate desc

2.

select feedback.Id,Title,Problem,feedback.User as feedbackUser,Content,reply.User as replyUser from feedback
left join reply
on feedback.Id = reply.FeedbackId
and feedback.Status=1 and reply.Status=1
order by feedback.CreateDate desc,reply.CreateDate desc

這兩條語句就一個詞只差,就是在on的連線條件後是用where還是用and.如果是where的話,這條語句就變成的內連線。

而使用and的話才能以左連線的形式返回記錄。 個人感覺這樣不是很清楚的分辨整個sql語句的where條件了,今天終於弄清楚先前使用左連線老是返回不是自己想要的結果的原因了。

http://hi.baidu.com/dyzhong/blog/item/b42e56a93c0bfcf71e17a2c0.html  從這裡受到啟發。。。

3.後面發現這樣還是錯誤的,要想完全看到左連線的效果,並帶上where條件,應該是下面的樣子

 

select feedback.Id,Title,Problem,feedback.User as feedbackUser,Content,reply.User as replyUser from feedback
left join reply
on feedback.Id = reply.FeedbackId
where feedback.Status=1 and (reply.Status=1 or reply.Status is NULL)
order by feedback.CreateDate desc,reply.CreateDate desc

 


(adsbygoogle = window.adsbygoogle || []).push({});

function googleAdJSAtOnload() {
var element = document.createElement(“script”);
element.src = “//pagead2.googlesyndication.com/pagead/js/adsbygoogle.js”;
element.async = true;
document.body.appendChild(element);
}
if (window.addEventListener) {
window.addEventListener(“load”, googleAdJSAtOnload, false);
} else if (window.attachEvent) {
window.attachEvent(“onload”, googleAdJSAtOnload);
} else {
window.onload = googleAdJSAtOnload;
}

資料庫 最新文章