{"id":137162,"date":"2020-07-22T08:45:32","date_gmt":"2020-07-22T00:45:32","guid":{"rendered":"http:\/\/4563.org\/?p=137162"},"modified":"2020-07-22T08:45:32","modified_gmt":"2020-07-22T00:45:32","slug":"%e6%95%b0%e6%8d%ae%e5%ba%93%e5%b7%a6%e8%bf%9e%e6%8e%a5%e5%92%8c%e5%8f%b3%e8%bf%9e%e6%8e%a5%e6%98%af%e5%8f%af%e4%bb%a5%e7%9b%b8%e4%ba%92%e8%bd%ac%e6%8d%a2%e7%9a%84%e5%90%a7%ef%bc%8c%e4%b8%8d%e5%a4%aa","status":"publish","type":"post","link":"http:\/\/4563.org\/?p=137162","title":{"rendered":"\u6570\u636e\u5e93\u5de6\u8fde\u63a5\u548c\u53f3\u8fde\u63a5\u662f\u53ef\u4ee5\u76f8\u4e92\u8f6c\u6362\u7684\u5427\uff0c\u4e0d\u592a\u786e\u5b9a\u7406\u89e3\u5bf9\u4e0d\u5bf9"},"content":{"rendered":"
\n
\n
\n

\u6570\u636e\u5e93\u5de6\u8fde\u63a5\u548c\u53f3\u8fde\u63a5\u662f\u53ef\u4ee5\u76f8\u4e92\u8f6c\u6362\u7684\u5427\uff0c\u4e0d\u592a\u786e\u5b9a\u7406\u89e3\u5bf9\u4e0d\u5bf9 <\/h1>\n

<\/p>\n

\n
\u8cc7\u6df1\u5927\u4f6c : git00ll <\/span> <\/i> 5<\/span> <\/div>\n
<\/div>\n<\/p><\/div>\n<\/p><\/div>\n<\/p><\/div>\n
<\/p>\n

\u8fd9\u4e24\u79cd\u5199\u6cd5\u662f\u7b49\u4ef7\u7684\u5417<\/p>\n

select a.*, b.* from A a left join B b where a.bid = b.id  select a.*, b.* from B b right join A a where a.bid = b.id  <\/code><\/pre>\n<\/p><\/div>\n
 \u5927\u4f6c\u6709\u8a71\u8aaa<\/b> (17<\/span>)        <\/div>\n
 <\/div>\n<\/p><\/div>\n<\/p><\/div>\n
    \n
  • \n
    \n
    \n
     \u8cc7\u6df1\u5927\u4f6c : saulshao <\/span>  <\/div>\n
     <\/i>            <\/span> <\/div>\n<\/p><\/div>\n
                                                                 \u8fd9\u4e2a\u662f\u4e0d\u662f\u7b49\u4ef7\u8981\u53d6\u51b3\u4e8e\u4f60\u7684\u8868\u8bbe\u8ba1\uff0c\u903b\u8f91\u4e0a\u662f\u6709\u5dee\u5f02\u7684\u3002
    \u5c31\u4f60\u7684 SQL \u800c\u8a00\uff0c\u5de6\u8fde\u63a5\u6211\u8bb0\u5f97 a \u8868\u4e2d\u5b58\u5728\uff0c\u4f46\u662f b \u8868\u4e2d\u4e0d\u5b58\u5728\u7684 id\uff0c\u4f1a\u88ab\u9009\u51fa\u3002\u53f3\u8fde\u63a5\u5219\u76f8\u53cd\u3002
    \u6362\u53e5\u8bdd\u8bf4\u4f60\u53ef\u80fd\u4f1a\u5f97\u5230\u4ee5\u4e0b\u7684\u4e00\u884c\u6570\u636e:
    (a.id,a.name,null,null,null)\u5176\u4e2d\u7684 null \u503c\u90fd\u6765\u81ea b \u8868\u4e2d\u4f60\u5047\u8bbe\u7684\u5b57\u6bb5\u3002<\/p>\n
    \u6211\u8bb0\u5f97 MS SQL server books online \u8bf4\u5f97\u5f88\u6e05\u695a\uff0c\u5173\u4e8e\u8fd9\u4e2a\u6982\u5ff5\u3002                                                            <\/p><\/div>\n<\/p><\/div>\n<\/li>\n
  • \n
    \n
    \n
     \u8cc7\u6df1\u5927\u4f6c : saulshao <\/span>  <\/div>\n
     <\/i>            <\/span> <\/div>\n<\/p><\/div>\n
                                                                 \u5982\u679c\u4f60\u7684 a,b \u4e24\u4e2a\u8868\u4e2d\u7684 id \u90fd\u662f\u552f\u4e00\u7d22\u5f15\uff0c\u7b49\u4ef7\u8fd9\u4e2a\u8bf4\u6cd5\u662f\u6210\u7acb\u7684\uff0c\u4f46\u662f\u5047\u5982 a \u8868\u4e2d\u7684 id \u5bf9\u5e94\u7684\u884c\u6bd4 b \u8868\u591a\uff0c\u6211\u8bb0\u5f97\u5de6\u8fde\u63a5\u7ed3\u679c\u4f1a\u4ee5 a \u8868\u4e2d\u7684\u884c\u4e3a\u51c6\uff0c\u53cd\u4e4b\u4e5f\u6210\u7acb                                                            <\/div>\n<\/p><\/div>\n<\/li>\n
  • \n
    \n
    \n
     \u8cc7\u6df1\u5927\u4f6c : EminemW <\/span>  <\/div>\n
     <\/i>            <\/span> <\/div>\n<\/p><\/div>\n
                                                                 *** a left join b ****\u4f1a\u4fdd\u7559 a \u8868\u5b58\u5728 \u4f46 b \u8868\u4e0d\u5b58\u5728\u7684\u6570\u636e                                                            <\/div>\n<\/p><\/div>\n<\/li>\n
  • \n
    \n
    \n
     \u8cc7\u6df1\u5927\u4f6c : qwertyzzz <\/span>  <\/div>\n
     <\/i>            <\/span> <\/div>\n<\/p><\/div>\n
                                                                 \u65b0\u5efa 2 \u4e2a\u8868\u6267\u884c\u4e0b\uff1f                                                            <\/div>\n<\/p><\/div>\n<\/li>\n
  • \n
    \n
    \n
     \u8cc7\u6df1\u5927\u4f6c : no1xsyzy <\/span>  <\/div>\n
     <\/i>            <\/span> <\/div>\n<\/p><\/div>\n
                                                                 @saulshao #2 \u6162\u70b9\uff0c\u4f60\u770b\u4e0b\u4e3b\u7684\uff0c\u5728\u8f6c\u6362\u5de6\u53f3\u7684\u540c\u65f6 A B \u4f4d\u7f6e\u4e5f\u5bf9\u8c03\u4e86\u2026\u2026
    \u6240\u4ee5\u662f\u4e00\u6837\u7684\u3002                                                            <\/div>\n<\/p><\/div>\n<\/li>\n
  • \n
    \n
    \n
     \u8cc7\u6df1\u5927\u4f6c : no1xsyzy <\/span>  <\/div>\n
     <\/i>            <\/span> <\/div>\n<\/p><\/div>\n
                                                                 \u8f93\u51fa\u7ed3\u679c\u662f\u4e00\u6837\u7684
    \u4f46\u4e2d\u95f4\u7684\u8fd0\u884c\u6b65\u9aa4\u662f\u5426\u4e00\u6837\u5c31\u4e0d\u6e05\u695a\u4e86\u3002\u76f4\u89c9\u4e0a\u5e94\u8be5\u4e0d\u4f1a\u6709\u4e0d\u540c\u3002\u5177\u4f53\u8981 explain\uff0c\u5fae\u5f31\u53ef\u80fd\u4f9d\u8d56\u4e8e\u7279\u5b9a\u5b9e\u73b0\u3002                                                            <\/div>\n<\/p><\/div>\n<\/li>\n
  • \n
    \n
    \n
     \u8cc7\u6df1\u5927\u4f6c : leoleoasd <\/span>  <\/div>\n
     <\/i>            <\/span> <\/div>\n<\/p><\/div>\n
                                                                 https:\/\/stackoverflow.com\/questions\/436345\/when-or-why-would-you-use-a-right-outer-join-instead-of-left                                                            <\/div>\n<\/p><\/div>\n<\/li>\n
  • \n
    \n
    \n
     \u8cc7\u6df1\u5927\u4f6c : leoleoasd <\/span>  <\/div>\n
     <\/i>            <\/span> <\/div>\n<\/p><\/div>\n
                                                                 \u770b\u4e0a\u53bb, \u9664\u4e86\u8bed\u4e49, \u6ca1\u5565\u4e0d\u540c?                                                            <\/div>\n<\/p><\/div>\n<\/li>\n
  • \n
    \n
    \n
     \u8cc7\u6df1\u5927\u4f6c : leoleoasd <\/span>  <\/div>\n
     <\/i>            <\/span> <\/div>\n<\/p><\/div>\n
                                                                 “There are no performance that can be gained if you’ll rearrange LEFT JOINs to RIGHT.”                                                            <\/div>\n<\/p><\/div>\n<\/li>\n
  • \n
    \n
    \n
     \u8cc7\u6df1\u5927\u4f6c : hemingyang <\/span>  <\/div>\n
     <\/i>            <\/span> <\/div>\n<\/p><\/div>\n
                                                                 \u4e0d\u662f \u7528 on \u5417 \u4e3a\u5565 \u4f60\u5199 where                                                            <\/div>\n<\/p><\/div>\n<\/li>\n
  • \n
    \n
    \n
     \u8cc7\u6df1\u5927\u4f6c : liujavamail <\/span>  <\/div>\n
     <\/i>            <\/span> <\/div>\n<\/p><\/div>\n
                                                                 \u6240\u4ee5 left join \u6216\u8005 right join \u53ea\u662f\u67e5\u8be2\u6570\u636e\u8303\u56f4\u5927\u5c0f\u4e0d\u4e00\u6837\uff0c\u4f46\u52a0\u4e86 where \u7684\u6761\u4ef6\u4e4b\u540e\uff0c \u6700\u540e\u90fd\u662f\u4e00\u6837\u7684\u7ed3\u679c\uff0c \u53ef\u4ee5\u8fd9\u4e48\u7406\u89e3\u5417\uff1f<\/p>\n
    \u5c31\u662f\u5982\u679c \u5de6\u8868\u6570\u636e\u5c11\uff0c\u5c31\u7528 left join, \u53f3\u8868\u6570\u636e\u5c11\uff0c\u5c31\u7528 right join                                                            <\/p><\/div>\n<\/p><\/div>\n<\/li>\n
  • \n
    \n
    \n
     \u8cc7\u6df1\u5927\u4f6c : saulshao <\/span>  <\/div>\n
     <\/i>            <\/span> <\/div>\n<\/p><\/div>\n
                                                                 \u6211\u4ee5\u524d\u4f3c\u4e4e\u662f\u5de6\u8868\u6570\u636e\u591a\u91c7\u7528 left join…..\u53cd\u4e4b\u7528 right join…
    \u4f46\u662f\u5047\u5982\u4f60\u662f\u771f\u7684\u8981\u6c42\u4ea4\u96c6\uff0c#11 \u7684\u8bf4\u6cd5\u662f\u6b63\u786e\u7684                                                            <\/div>\n<\/p><\/div>\n<\/li>\n
  • \n
    \n
    \n
     \u8cc7\u6df1\u5927\u4f6c : zhangysh1995 <\/span>  <\/div>\n
     <\/i>            <\/span> <\/div>\n<\/p><\/div>\n
                                                                 @git00ll 
    \u4e00\u53e5\u56de\u7b54\uff1aA LEFT JOIN B \u548c B RIGHT JOIN A \u662f\u7b49\u4ef7\u7684\u3002LEFT JOIN \u4fdd\u7559\u5de6\u8868\u6240\u6709\u884c\uff0cRIGHT JOIN \u4fdd\u7559\u53f3\u8fb9\u6240\u6709\u884c\u3002
    \u6211\u81ea\u5df1\u521a\u5199\u8fc7\u4e00\u7bc7\u7406\u89e3 JOIN \u7684\u6587\u7ae0\uff0c\u6b22\u8fce\u8d4f\u4e2a\u8138 https:\/\/zhuanlan.zhihu.com\/p\/157249501<\/p>\n
    @hemingyang 
    WHERE \u548c ON \u5728 JOIN \u60c5\u51b5\u4e0b\u6ca1\u6709\u4ec0\u4e48\u533a\u522b\u3002SQL Server \u4e0d\u592a\u4e86\u89e3\uff0c\u5728 MySQL \u91cc\u9762\uff0cON \u90e8\u5206\u7684\u6761\u4ef6\u4e5f\u53ef\u4ee5\u5199 WHERE \u91cc\u9762\uff0c\u4f46\u662f\u4e60\u60ef\u662f ON \u5199 JOIN \u6761\u4ef6\uff0cWHERE \u5199\u7ed3\u679c\u6761\u4ef6\u3002\u6587\u6863\u5982\u4e0b\uff1a<\/p>\n
    The search_condition used with ON is any conditional expression of the form that can be used in a WHERE clause. Generally, the ON clause serves for conditions that specify how to join tables, and the WHERE clause restricts which rows to include in the result set.                                                            <\/p><\/div>\n<\/p><\/div>\n<\/li>\n
  • \n
    \n
    \n
     \u8cc7\u6df1\u5927\u4f6c : JasonLaw <\/span>  <\/div>\n
     <\/i>            <\/span> <\/div>\n<\/p><\/div>\n
                                                                 @zhangysh1995 #13 <\/p>\n
    \u4f60\u8bf4\u201cWHERE \u548c ON \u5728 JOIN \u60c5\u51b5\u4e0b\u6ca1\u6709\u4ec0\u4e48\u533a\u522b\u201d\uff0c\u4f60\u662f\u8bf4 inner join \u5417\uff1f\u5982\u679c\u662f\u7684\u8bdd\uff0c\u4f60\u8bf4\u5f97\u6ca1\u9519\uff0c\u4f46\u662f\u6211\u60f3\u66f4\u52a0\u660e\u786e\u5730\u8bf4\u51fa\uff0cwhere \u548c on \u5728 outer join \u7684\u8868\u73b0\u662f\u6709\u533a\u522b\u7684\u3002<\/p>\n
    \u5728\u201cDatabase System Concepts 6th edition – Chapter 4 Intermediate SQL – 4.1 Join Expressions – 4.1.1 Join Conditions\u201d\u4e2d\uff0c\u8bf4\u4e86\u201cHowever, there are two good reasons for introducing the on condition. First, we shall see shortly that for a kind of join called an outer join, on conditions do behave in a manner different from where conditions. Second, an SQL query is often more readable by humans if the join condition is specified in the on clause and the rest of the conditions appear in the where clause.\u201d\u3002\u4e4b\u540e\u5728\u201c4.1.2 Outer Joins\u201d\u4e2d\uff0c\u5b83\u8bf4\u201cAs we noted earlier, on and where behave differently for outer join. The reason for this is that outer join adds null-padded tuples only for those tuples that do not contribute to the result of the corresponding inner join. The on condition is part of the outer join specification, but a where clause is not.\u201d\u3002<\/p>\n
    \u6211\u8fd9\u91cc\u7528\u4f8b\u5b50\u6f14\u793a\u4e00\u4e0b on \u548c where \u7684\u533a\u522b\u5427\u3002
    create table t1(c1 int);
    create table t2(c2 int);
    insert into t1 values(1),(2),(3);
    insert into t2 values(1);<\/p>\n
    select t1.*, t2.* from t1 left join t2 on t1.c1 = t2.c2; \u7684\u7ed3\u679c\u4e3a
    +——+——+
    | c1 | c2 |
    +——+——+
    | 1 | 1 |
    | 2 | NULL |
    | 3 | NULL |
    +——+——+<\/p>\n
    select t1.*, t2.* from t1 left join t2 on true where t1.c1 = t2.c2; \u7684\u7ed3\u679c\u4e3a
    +——+——+
    | c1 | c2 |
    +——+——+
    | 1 | 1 |
    +——+——+                                                            <\/div>\n<\/p><\/div>\n<\/li>\n
  • \n
    \n
    \n
     \u8cc7\u6df1\u5927\u4f6c : JasonLaw <\/span>  <\/div>\n
     <\/i>            <\/span> <\/div>\n<\/p><\/div>\n
                                                                 @no1xsyzy #6 
    @leoleoasd #9 <\/p>\n
    \u867d\u7136\u8bf4 a left join b on a.bid = b.id \u548c b right join a on a.bid = b.id \u5f97\u5230\u7684\u7ed3\u679c\u662f\u4e00\u6837\u7684\uff0c\u4f46\u662f\u56e0\u4e3a\u5b83\u4eec\u7684 a \u548c b \u7684\u987a\u5e8f\u8fd8\u662f\u4f1a\u9020\u6210\u5904\u7406\u7684\u6027\u80fd\u5dee\u5f02\u7684\uff0c\u5177\u4f53\u53ef\u4ee5\u770b\u770b\u201cDatabase System Concepts 6th edition – Chapter 12 Query Processing – 12.5 Join Operation\u201d\uff0c\u56e0\u4e3a\u5185\u5bb9\u662f\u5728\u592a\u591a\u4e86\uff0c\u5728\u8bc4\u8bba\u91cc\u8bb2\u4e0d\u6e05\u695a\u3002                                                            <\/p><\/div>\n<\/p><\/div>\n<\/li>\n
  • \n
    \n
    \n
     \u8cc7\u6df1\u5927\u4f6c : zhangysh1995 <\/span>  <\/div>\n
     <\/i>            <\/span> <\/div>\n<\/p><\/div>\n
                                                                 @JasonLaw \u6211\u6ca1\u6709\u8bf4 select t1.*, t2.* from t1 left join t2 on t1.c1 = t2.c2; \u548c select t1.*, t2.* from t1 left join t2 on true where t1.c1 = t2.c2; \u662f\u4e00\u6837\u7684\u3002\u5f88\u660e\u663e\u4f60\u7ed9\u7684\u4e24\u6761\u5e76\u4e0d\u662f\u7b49\u4ef7\u7684\u3002\u6211\u6307\u7684\u662f\u540c\u4e00\u4e2a\u6761\u4ef6\u653e\u5728 on \u6216\u8005\u653e\u5728 where \u662f\u4e00\u6837\u7684\uff0c\u8bf7\u4e0d\u8981\u66f2\u89e3\u610f\u601d\u3002<\/p>\n
    \u8fd9\u4e24\u6761\u662f\u7b49\u4ef7\u7684\u3002
    select t1.*, t2.* from t1 left join t2 on t1.c1 = t2.c2;
    select t1.*, t2.* from t1 left join t2 where t1.c1 = t2.c2;                                                            <\/div>\n<\/p><\/div>\n<\/li>\n
  • \n
    \n
    \n
     \u8cc7\u6df1\u5927\u4f6c : JasonLaw <\/span>  <\/div>\n
     <\/i>            <\/span> <\/div>\n<\/p><\/div>\n
                                                                 @zhangysh1995 \u4f60\u53ef\u4ee5\u81ea\u5df1\u53bb\u6267\u884c\u4e00\u4e0b\uff0c\u770b\u770b\u662f\u4e0d\u662f\u7b49\u4ef7\u7684\uff0c\u5982\u679c\u662f\u7684\u8bdd\uff0c\u9ebb\u70e6\u5206\u4eab\u4e00\u4e0b\u8bed\u53e5\uff0c\u8ba9\u6211\u91cd\u73b0\u4e00\u4e0b\u3002                                                            <\/div>\n<\/p><\/div>\n<\/li>\n
  • \n","protected":false},"excerpt":{"rendered":"
    \u6570\u636e\u5e93\u5de6\u8fde\u63a5\u548c\u53f3\u8fde\u63a5\u662f\u53ef\u4ee5\u76f8\u4e92\u8f6c\u6362…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[],"tags":[],"_links":{"self":[{"href":"http:\/\/4563.org\/index.php?rest_route=\/wp\/v2\/posts\/137162"}],"collection":[{"href":"http:\/\/4563.org\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/4563.org\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/4563.org\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/4563.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=137162"}],"version-history":[{"count":0,"href":"http:\/\/4563.org\/index.php?rest_route=\/wp\/v2\/posts\/137162\/revisions"}],"wp:attachment":[{"href":"http:\/\/4563.org\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=137162"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/4563.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=137162"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/4563.org\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=137162"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}