这是本节的多页打印视图。点击此处打印.

内联接

1: 等联接和非等联接
2: 自然联接
3: 交叉联接

内联接基于联接谓词将两个表中的记录组合在一起，并要求第一个表中每个记录在第二个表中都具有匹配的记录。因此，内联接只会返回两个已联接表中符合联接条件的记录。不包含匹配项的记录将从结果集中排除。

内联接采用以下格式：

SELECT column‑list FROM left-join-table
  [INNER] JOIN right-join-table ON join-predicate

如果您省略 INNER 关键字，则 Vertica 假定内联接。内联接是可交换的和关联的。您可以按任何顺序指定表而不更改结果。

示例

以下示例指定表 store.store_dimension 和 public.employee_dimension 之间的内联接，其记录分别在 store_region 和 employee_region 列中具有匹配值。

=> SELECT s.store_region, SUM(e.vacation_days) TotalVacationDays
   FROM public.employee_dimension e
   JOIN store.store_dimension s ON s.store_region=e.employee_region
   GROUP BY s.store_region ORDER BY TotalVacationDays;

这种联接也可以表示如下：

=> SELECT s.store_region, SUM(e.vacation_days) TotalVacationDays
    FROM public.employee_dimension e, store.store_dimension s
    WHERE s.store_region=e.employee_region
    GROUP BY s.store_region ORDER BY TotalVacationDays;

两个查询返回相同的结果集：

 store_region | TotalVacationDays
--------------+-------------------
 NorthWest    |             23280
 SouthWest    |            367250
 MidWest      |            925938
 South        |           1280468
 East         |           1952854
 West         |           2849976
(6 rows)

如果联接的内表 store.store_dimension 有任何行的 store_region 值与表 public.employee_dimension 中的 employee_region 值不匹配，则从结果集中排除这些行。要包含该行，您可以指定外联接。

1 - 等联接和非等联接

Vertica 支持含有匹配列值和非匹配列值的任意联接表达式。例如：

SELECT * FROM fact JOIN dim ON fact.x = dim.x;
SELECT * FROM fact JOIN dim ON fact.x > dim.y;
SELECT * FROM fact JOIN dim ON fact.x <= dim.y;
SELECT * FROM fact JOIN dim ON fact.x <> dim.y;
SELECT * FROM fact JOIN dim ON fact.x <=> dim.y;

注意

= 和 <=> 运算符的运行速度通常是最快的。

等联接以等式（匹配列值）为基础。此等式以等于号 (=) 表示，它在使用 SQL-92 语法的 ON 子句或使用更旧的联接语法的 WHERE 子句中用作比较运算符。

下文中第一个示例使用 SQL-92 和 ON 子句将线上销售额表与使用呼叫中心键的呼叫中心表联接在一起，然后查询返回等于 156 的销售日期键：

=> SELECT sale_date_key, cc_open_date FROM online_sales.online_sales_fact
   INNER JOIN online_sales.call_center_dimension
   ON (online_sales.online_sales_fact.call_center_key =
    online_sales.call_center_dimension.call_center_key
   AND sale_date_key = 156);
 sale_date_key | cc_open_date
---------------+--------------
           156 | 2005-08-12
(1 row)

第二个示例使用更旧的联接语法和 WHERE 子句联接上述表，获得了相同的结果：

=> SELECT sale_date_key, cc_open_date
    FROM online_sales.online_sales_fact, online_sales.call_center_dimension
   WHERE online_sales.online_sales_fact.call_center_key =
      online_sales.call_center_dimension.call_center_key
   AND sale_date_key = 156;
 sale_date_key | cc_open_date
---------------+--------------
           156 | 2005-08-12
(1 row)

Vertica 还允许使用包含复合（多列）主键和外键的表。例如，要创建一对包含多列键的表：

=> CREATE TABLE dimension(pk1 INTEGER NOT NULL, pk2 INTEGER NOT NULL);=> ALTER TABLE dimension ADD PRIMARY KEY (pk1, pk2);
=> CREATE TABLE fact (fk1 INTEGER NOT NULL, fk2 INTEGER NOT NULL);
=> ALTER TABLE fact ADD FOREIGN KEY (fk1, fk2) REFERENCES dimension (pk1, pk2);

要联接使用复合键的表，您必须用 Boolean AND 运算符连接两个联接谓词。例如：

=> SELECT * FROM fact f JOIN dimension d ON f.fk1 = d.pk1 AND f.fk2 = d.pk2;

您可以通过含有 NULL=NULL 联接的 <=> 运算符的表达式编写查询。

=> SELECT * FROM fact JOIN dim ON fact.x <=> dim.y;

<=> 运算符与 = 运算符一样，执行等式比较，但它在两个操作数都为 NULL 时返回 true（而不是 NULL），并在一个操作数为 NULL 时返回 false（而不是 NULL）。

=> SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
 ?column? | ?column? | ?column?
----------+----------+----------
 t        | t        | f
(1 row)

比较 <=> 运算符和 = 运算符：

=> SELECT 1 = 1, NULL = NULL, 1 = NULL;
 ?column? | ?column? | ?column?
----------+----------+----------
 t        |          |
(1 row)

注意

对主键/外键组合编写 NULL=NULL 联接不是最佳选择，因为 PK/FK 列通常定义为 NOT NULL。

编写联接时，它可帮助您提前知道哪些列包含 NULL 值。例如，员工的雇佣日期就不是一个好选择，因为雇佣日期不可能被忽略。但是，如果一些员工按小时领工资，而一些员工按薪水领工资，这种情况下，可以使用每小时工资列。如果您不确定给定表中的列值，而且希望检查一下，请键入以下命令：

=> SELECT COUNT(*) FROM tablename WHERE columnname IS NULL;

2 - 自然联接

自然联接只是一种包含隐式联接谓词的联接。自然联接可以是内联接、左外联接、右外联接或全外联接，它采用以下格式：

SELECT column‑list FROM left-join-table
NATURAL [ INNER | LEFT OUTER | RIGHT OUTER | FULL OUTER ] JOIN right-join-table

默认情况下，自然联接为自然内联接；但自然联接也可以为自然左/右/全外联接。内联接和自然联接的主要区别在于，内联接具有显式联接条件，而自然联接的条件的形成方式是，对具有相同名称和兼容数据类型的表中所有列对进行匹配，使自然联接变为等联接，因为联接条件在普通的列之间是相等的。（如数据类型不兼容，Vertica 将返回错误。）

注意

数据类型强制转换表列出了哪些数据类型可以转换为其他数据类型。如果一种数据类型可转换为其他数据类型，则这两种数据类型兼容。

当 T2 列 val 大于 5 时，以下查询是 T1 表和 T2 表之间的一个简单的自然联接：

=> SELECT * FROM T1 NATURAL JOIN T2 WHERE T2.val > 5;

store_sales_fact 表和 product_dimension 表有两列共享相同的名称和数据类型：product_key 和 product_version。以下示例在这两个表的共享列处创建自然联接：

=> SELECT product_description, sales_quantity FROM store.store_sales_fact
   NATURAL JOIN public.product_dimension;

以下三个查询返回相同的结果，分别表示为基本查询、内联接和自然联接。仅当 store_sales_fact 表和 store_dimension 表中的公共属性为 store_key 时，表的表达式才等效。如果两个表都具有名为 store_key 的列，则自然联接也会具有 store_sales_fact.store_key = store_dimension.store_key 联接条件。由于所有三个实例的结果都相同，因此它们仅显示在第一个（基本）查询中：

=> SELECT store_name FROM store.store_sales_fact, store.store_dimension
   WHERE store.store_sales_fact.store_key = store.store_dimension.store_key
   AND store.store_dimension.store_state = 'MA' ORDER BY store_name;
 store_name
------------
 Store11
 Store128
 Store178
 Store66
 Store8
 Store90
(6 rows)

作为内联接编写的查询：

=> SELECT store_name FROM store.store_sales_fact
   INNER JOIN store.store_dimension
   ON (store.store_sales_fact.store_key = store.store_dimension.store_key)
   WHERE store.store_dimension.store_state = 'MA' ORDER BY store_name;

对于自然联接，通过对由相同的列名称联接的两个表中所有列进行比较，隐式显示联接谓词。结果集对表示一对命名相同的列仅保留一列。

=> SELECT store_name FROM store.store_sales_fact
   NATURAL JOIN store.store_dimension
   WHERE store.store_dimension.store_state = 'MA' ORDER BY store_name;

3 - 交叉联接

交叉联接是一种编写起来最简单的联接，但运行速度通常不是最快的，因为它们包含两个表中记录的所有可能组合。交叉联接不包含任何联接条件，它会返回笛卡尔积，其中结果集中的行数等于第一个表中的行数与第二个表中的行数之乘积。

以下查询返回了促销表和商店销售表的所有可能组合：

=> SELECT * FROM promotion_dimension CROSS JOIN store.store_sales_fact;

由于此示例返回了超过 6 亿个记录，因此，许多交叉联接的结果非常大，不易管理。但是，交叉联接非常有用，例如当您想返回一个单行结果集时。

提示

通过使用 WHERE 子句联接谓词过滤掉交叉联接中不需要的记录：

=> SELECT * FROM promotion_dimension p   CROSS JOIN store.store_sales_fact f
   WHERE p.promotion_key LIKE f.promotion_key;

隐式联接与显式联接

Vertica 建议您不要编写隐式交叉联接（FROM 子句中以逗号分隔的表）。这些查询可能会意外忽略了某个联接谓词。

以下查询隐式交叉联接 promotion_dimension 和 store.store_sales_fact 表：

=> SELECT * FROM promotion_dimension, store.store_sales_fact;

更好的做法是明确表达此交叉联接，如下所示：

=> SELECT * FROM promotion_dimension CROSS JOIN store.store_sales_fact;

示例

以下示例创建了两个小表格及其超投影，然后对这两个表运行交叉联接：

=> CREATE TABLE employee(employee_id INT, employee_fname VARCHAR(50));
=> CREATE TABLE department(dept_id INT, dept_name VARCHAR(50));
=> INSERT INTO employee VALUES (1, 'Andrew');
=> INSERT INTO employee VALUES (2, 'Priya');
=> INSERT INTO employee VALUES (3, 'Michelle');
=> INSERT INTO department VALUES (1, 'Engineering');
=> INSERT INTO department VALUES (2, 'QA');
=> SELECT * FROM employee CROSS JOIN department;

在结果集中，交叉联接检索了第一个表中的记录，然后为第二个表中每个行创建一个新行。接着，它对第一个表中的下一个记录重复此操作，依此类推。

 employee_id | employee_name | dept_id | dept_name
 -------------+---------------+---------+-----------
           1 | Andrew        |       1 |  Engineering
           2 | Priya         |       1 |  Engineering
           3 | Michelle      |       1 |  Engineering
           1 | Andrew        |       2 |  QA
           2 | Priya         |       2 |  QA
           3 | Michelle      |       2 |  QA
(6 rows)