=Start=<\/p>\n\n\n\n
\u6574\u7406\u8bb0\u5f55\u4e00\u4e0b\u6700\u8fd1\u5728\u4f7f\u7528left join\u65f6\u78b0\u5230\u7684\u5751\uff0c\u65b9\u4fbf\u4ee5\u540e\u53c2\u8003\uff0c\u907f\u514d\u4ee5\u540e\u518d\u51fa\u73b0\u5b9e\u9645\u548c\u9884\u671f\u4e0d\u4e00\u81f4\u7684\u60c5\u51b5\u3002<\/p>\n\n\n\n
\u5728\u5b66\u4e60SQL\u7684\u5de6\u8fde\u63a5(left join)\u65f6\u770b\u5230\u7684\u4e00\u53e5\u8bdd\uff0c\u8ba9\u6211\u5feb\u901f\u7684\u4e86\u89e3\u4e86\u5b83\u7684\u529f\u80fd\uff1a<\/p>\n\n\n\n
\u4ee5LEFT [OUTER] JOIN\u5173\u952e\u5b57\u524d\u9762\u7684\u8868\u4f5c\u4e3a\u4e3b\u8868\uff0c\u548c\u5176\u4ed6\u8868\u8fdb\u884c\u5173\u8054\uff0c\u8fd4\u56de\u8bb0\u5f55\u548c\u4e3b\u8868\u7684\u8bb0\u5f55\u6570\u4e00\u81f4<\/em>\uff0c\u5173\u8054\u4e0d\u4e0a\u7684\u5b57\u6bb5\u7f6e\u4e3aNULL\u3002<\/strong><\/p><\/blockquote>\n\n\n\n
\u4f46\u662f\u5b83\u7684\u7ed3\u8bba\u4e0d\u603b\u662f<\/strong>\u6b63\u786e\u7684\uff01<\/p>\n\n\n\n
\u5728\u4f7f\u7528left join\u7684\u65f6\u5019\uff0c\u4e00\u5b9a\u8981\u6ce8\u610f\u88ab\u5173\u8054\u8868\uff08\u9664\u4e86\u7b2c\u4e00\u5f20\u8868\u4e4b\u5916\u7684\u8868\uff09<\/strong>\u7684\u6570\u636e\u91cc\u9762\u6709\u6ca1\u6709\u91cd\u590d\u7684\u5185\u5bb9\uff0c\u5982\u679c\u6709\u7684\u8bdd\uff0c\u7ed3\u679c\u8bb0\u5f55\u548c\u4e3b\u8868\u7684\u8bb0\u5f55\u5c31\u4e0d\u4e00\u5b9a\u4e00\u81f4\uff08\u5373\u4fbf\u4f7f\u7528distinct\u5173\u952e\u5b57\u53bb\u91cd\uff09\u3002<\/p>\n\n\n\n
\u4e0b\u9762\u4ee52\u5f20\u6d4b\u8bd5\u8868\u505aleft join\u6765\u8bf4\u660e\u4e00\u4e0b\u3002<\/p>\n\n\n\n
tableA\u7684\u5185\u5bb9\u5982\u4e0b\uff1a<\/p>\n\n\n\n
<\/td>col1<\/td> col2<\/td> col3<\/td><\/tr> 1<\/td> a1<\/td> x<\/td> y<\/td><\/tr> 2<\/td> a2<\/td> x<\/td> z<\/td><\/tr> 3<\/td> a3<\/td> y<\/td> y<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n tableB\u7684\u5185\u5bb9\u5982\u4e0b\uff1a<\/p>\n\n\n\n
<\/td>col1<\/td> col2<\/td><\/tr> 1<\/td> a1<\/td> m<\/td><\/tr> 2<\/td> a1<\/td> n<\/td><\/tr> 3<\/td> a2<\/td> m<\/td><\/tr> 4<\/td> a2<\/td> m<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n \u6b64\u65f6\u8fdb\u884cleft [outer] join\u7684\u6d4b\u8bd5SQL\uff1a<\/p>\n\n\n\n
select
x1.*, x2.*<\/em>
from
(select
*
from
tableA
)x1
left outer join
(select
*
from
tableB
)x2
on x1.col1 = x2.col1
;<\/code><\/p>\n\n\n\n\u5f53tableB\u4e2d\u6ca1\u6709\u7b2c4\u884c\uff08a2,m\uff09\u65f6\uff0c\u4e24\u8868join\u7684\u7ed3\u679c\uff08\u67094\u884c\uff09\u5982\u4e0b\uff1a<\/p>\n\n\n\n
1 <\/td> a1 <\/td> x <\/td> y <\/td> a1 <\/td> m <\/td><\/tr> 2 <\/td> a1 <\/td> x <\/td> y <\/td> a1 <\/td> n <\/td><\/tr> 3 <\/td> a2 <\/td> x <\/td> z <\/td> a2 <\/td> m <\/td><\/tr> 4<\/td> a3 <\/td> y <\/td> y <\/td> NULL <\/td> NULL <\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n \u53ef\u4ee5\u770b\u5230\uff0c\u5176\u7ed3\u679c\u4e3a4\u884c\uff0c\u6bd4tableA\u76843\u884c\u8981\u591a\u51fa1\u884c\uff0c\u56e0\u4e3a\u5728tableB\u4e2d\u5728col1\u5217\u91cc\u9762\u7684a1\u5b57\u6bb5\u6709\u91cd\u590d\u9879\uff08\u867d\u7136\u6574\u884c\u7684\u5185\u5bb9\u5e76\u4e0d\u91cd\u590d\uff09\u3002<\/strong><\/p>\n\n\n\n
\u5f53\u5728tableB\u4e2d\u52a0\u5165\u7b2c4\u884c\u540e\uff0c\u7ed3\u679c\u5c31\u518d\u589e\u52a0\u4e861\u884c\uff08\u67095\u884c\uff09\uff0c\u5982\u679c\u7528distinct\u5173\u952e\u5b57\u53bb\u91cd\u7684\u8bdd\u4f1a\u6ee4\u6389\u5bf9\u5e94\u91cd\u590d\u7684\u5185\u5bb9\uff08\u67094\u884c\uff0c\u4f46\u4f9d\u7136\u6bd4tableA\u76843\u884c\u8981\u591a1\u884c\uff09\uff1a<\/p>\n\n\n\n
select distinct
x1.col1,
x1.col2,
x1.col3,
x2.col1 as x2_col1,
x2.col2 as x2_col2
from
(select
*
from
tableA
)x1
left outer join
(select
*
from
tableB
)x2
on x1.col1 = x2.col1
;<\/code><\/p>\n\n\n\n\u6240\u4ee5\uff0c\u5728\u4f7f\u7528left join\u7684\u65f6\u5019\uff0c\u4e00\u5b9a\u8981\u6ce8\u610f\u88ab\u5173\u8054\u8868\u7684\u6570\u636e\u91cc\u9762\u6709\u6ca1\u6709\u91cd\u590d\u7684\u5185\u5bb9\uff0c\u5982\u679c\u6709\u7684\u8bdd\uff0c\u7ed3\u679c\u8bb0\u5f55\u548c\u4e3b\u8868\u7684\u8bb0\u5f55\u5c31\u4e0d\u4e00\u5b9a\u4e00\u81f4\uff08\u5373\u4fbf\u4f7f\u7528distinct\u5173\u952e\u5b57\u53bb\u91cd\uff09\u3002<\/p>\n\n\n\n
\u53c2\u8003\u94fe\u63a5\uff1a<\/h5>\n\n\n\n