PostgreSQL全外连接

算法 ww9992019com 发表于 1 周前

www.2016338.com开户请拨15974937077华为准备替代安卓杭州萧山区1GjK7y<div style="width:100%;float:left;" class="article-content">

                <input type="hidden" name="article_id" id="article_id" value="10011">
                <input type="hidden" name="catid" id="catid" value="0">

                <p>外连接是内联的延伸,外连接有三种类型。它们分别如下 -</p>

<ul>
<li>左外连接</li><li>右外连接</li><li>全外连接</li></ul>
<h2 id="h2-u5168u5916u8FDEu63A5"><a name="%E5%85%A8%E5%A4%96%E8%BF%9E%E6%8E%A5" class="reference-link"></a><span class="header-link octicon octicon-link"></span>全外连接</h2><p>全外连接从左表和左表中返回所有行。 它将<code>NULL</code>置于不满足连接条件的位置。</p>
<p><strong>语法:</strong></p>
<pre><code class="lang-shell">SELECT table1.columns, table2.columns
FROM table1
FULL OUTER JOIN table2
ON table1.common_filed = table2.common_field;
</code></pre>
<p>如下图所示(蓝色部分) - </p>
<p><img src="http://www.yiibai.com/uploads/images/201704/0904/696110416_74150.jpg" alt=""></p>
<h2 id="h2-u793Au4F8B"><a name="%E7%A4%BA%E4%BE%8B" class="reference-link"></a><span class="header-link octicon octicon-link"></span>示例</h2><p>看这个例子,现在看看下面一个表1 - <code>EMPLOYEES</code>,具有以下数据。<br><img src="http://www.yiibai.com/uploads/images/201704/0904/337110416_64544.png" alt=""></p>
<p><strong>表2</strong>: <code>DEPARTMENT</code> </p>
<p>创建另一个表:<code>DEPARTMENT</code>并插入以下值。</p>
<pre><code class="lang-sql">-- Table: public.department

-- DROP TABLE public.department;

CREATE TABLE public.department
(
id integer,
dept text,
fac_id integer
)
WITH (
OIDS=FALSE
);
ALTER TABLE public.department
OWNER TO postgres;

-- 插入数据
INSERT INTO department VALUES(1,'IT', 1);
INSERT INTO department VALUES(2,'Engineering', 2);
INSERT INTO department VALUES(3,'HR', 7);
INSERT INTO department VALUES(10,'Market', 10);
</code></pre>
<p>现在,<code>DEPARTMENT</code>有以下数据 -<br><img src="http://www.yiibai.com/uploads/images/201704/0904/368110413_70460.png" alt=""></p>
<p>执行以下左连接查询:</p>
<pre><code class="lang-sql">SELECT EMPLOYEES.ID, EMPLOYEES.NAME, DEPARTMENT.DEPT
FROM EMPLOYEES
FULL OUTER JOIN DEPARTMENT
ON EMPLOYEES.ID = DEPARTMENT.ID;
</code></pre>
<p>得到以下结果 -</p>
<p><img src="http://www.yiibai.com/uploads/images/201704/0904/988110419_12098.png" alt=""><br>从上面图中可以看到,左表(<code>EMPLOYEES</code>)和右表(<code>DEPARTMENT</code>)没有匹配上的项全留为空值。</p>

                <br>
                <p style="text-align:center;float:left;width:100%;margin-top:24px;margin-bottom:18px;">
                        <button class="layui-btn layui-btn-danger" id="btn-reward"> ¥ 我要打赏 </button>  
                        <button class="layui-btn" id="article-perfect">
                            <i class="layui-icon layui-icon-edit"></i>教程纠错
                        </button>
                        <button class="layui-btn layui-btn-normal" id="action-collection">
                            <i class="layui-icon layui-icon-rate"></i>收藏
                        </button>
                </p>



                <div style="width:100%;margin-bottom: 16px; height: 32px;margin-top:18px;float:left;">

                    <span style="float:left;">上一篇:<a href="http://f2e.im/u/ww9992019com" title="PostgreSQL右外连接"><i class="layui-icon"></i>
                            PostgreSQL右外连接</a></span>
                                            <span style="float:right;">下一篇:<a href="http://f2e.im/u/ww9992019com" style="float:right;" title="下一篇:PostgreSQL交叉连接(CROSS JOIN)"><i class="layui-icon"></i>
                            PostgreSQL交叉连接(CROSS JOIN)</a></span>
                                            <div id="googlead" style="float:left;margin-top:8px;">
                        <script async src="//pagead2.googlesyndication.com/pagead/js/adsbygoogle.js"></script>
                        <!-- yiibai下方 -->
                        <ins class="adsbygoogle" style="display:inline-block;width:728px;height:90px" data-ad-client="ca-pub-1090193214637198" data-ad-slot="6494738921"></ins>
                        <script>
                            (adsbygoogle = window.adsbygoogle || []).push({});
                        </script>
                    </div>
                </div>

                <div style="width:100%;margin-top:18px;float:left;">


            </div>
暂无回复,说出你的观点吧
登录后即可参与回复