左外连接

在左外连接的语法中，外连接的控制表显示在开始外连接的关键字左边。左外连接返回连接条件为 true 的所有行，除此之外，还返回控制表中的所有其它行并将从属表中的相应值显示为 NULL。

下列查询使用 ANSI 语法 LEFT OUTER JOIN 来获取与图 1（它使用 GBase 8s 外连接语法）相同的结果：

图: 查询

SELECT c.customer_num, c.lname, c.company, c.phone, 
          u.call_dtime, u.call_descr
          FROM customer c LEFT OUTER JOIN cust_calls u 
          ON c.customer_num = u.customer_num;

在此示例中，可以使用 ON 子句来指定连接条件。可以在 WHERE 子句中添加其它过滤器来限制结果集；此类过滤器是后连接（post-join）过滤器。

以下查询只返回客户没有致电客户服务中心的行。在此查询中，数据库服务器对 customer 和 cust_calls 表的 customer_num 列执行外连接之后在 WHERE 子句中应用过滤器。

图: 查询

SELECT c.customer_num, c.lname, c.company, c.phone, 
          u.call_dtime, u.call_descr 
          FROM customer c LEFT OUTER JOIN cust_calls u 
          ON c.customer_num = u.customer_num 
          WHERE u.customer_num IS NULL;

除了前面的示例之外，下列示例显示了可与 ANSI 连接语法配合使用的各种查询构造类型：

SELECT *
          FROM (t1 LEFT OUTER JOIN (t2 LEFT OUTER JOIN t3 ON t2.c1=t3.c1)
          ON t1.c1=t3.c1) JOIN (t4 LEFT OUTER JOIN t5 ON t4.c1=t5.c1)
          ON t1.c1=t4.c1;
          
          SELECT *
          FROM (t1 LEFT OUTER JOIN (t2 LEFT OUTER JOIN t3 ON t2.c1=t3.c1) 
          ON t1.c1=t3.c1), 
          (t4 LEFT OUTER JOIN t5 ON t4.c1=t5.c1)
          WHERE t1.c1 = t4.c1;
          
          SELECT *
          FROM (t1 LEFT OUTER JOIN (t2 LEFT OUTER JOIN t3 ON t2.c1=t3.c1)
          ON t1.c1=t3.c1) LEFT OUTER JOIN (t4 JOIN t5 ON t4.c1=t5.c1)
          ON t1.c1=t4.c1;
          
          SELECT *
          FROM t1 LEFT OUTER JOIN (t2 LEFT OUTER JOIN t3 ON t2.c1=t3.c1)
          ON t1.c1=t2.c1;
          
          SELECT *
          FROM t1 LEFT OUTER JOIN (t2 LEFT OUTER JOIN t3 ON t2.c1=t3.c1)
          ON t1.c1=t3.c1;
          
          SELECT *
          FROM (t1 LEFT OUTER JOIN t2 ON t1.c1=t2.c1)
          LEFT OUTER JOIN t3 ON t2.c1=t3.c1;
          
          SELECT *
          FROM (t1 LEFT OUTER JOIN t2 ON t1.c1=t2.c1)
          LEFT OUTER JOIN t3 ON t1.c1=t3.c1;
          
          SELECT *
          FROM t9, (t1 LEFT JOIN t2 ON t1.c1=t2.c1),
          (t3 LEFT JOIN t4 ON t3.c1=10), t10, t11,
          (t12 LEFT JOIN t14 ON t12.c1=100);
          
          SELECT * FROM
          ((SELECT c1,c2 FROM t3) AS vt3(v31,v32)
          LEFT OUTER JOIN
          ( (SELECT c1,c2 FROM t1) AS vt1(vc1,vc2)
          LEFT OUTER JOIN
          (SELECT c1,c2 FROM t2) AS vt2(vc3,vc4)
          ON vt1.vc1 = vt2.vc3)
          ON vt3.v31 = vt2.vc3);

上面最后一个示例说明了关于派生表的连接。它指定将外查询的 FROM 子句中子查询的结果和另一个其它两个子查询结果的左外连接的结果进行左外连接。请参阅FROM 子句中的子查询获得较为简单的符合 ANSI 语法的子查询示例。