SQL 自然连接 POSTGRES

Question

我不确定我需要什么样的连接，因为我不熟悉尝试以这种方式重叠数据，或者它是否可行。

我有两个表，它们共享一组相似的数据，并且都通过 Room_id 与第三个父表相关。

我有一个名为 Room_rates 的表，它存储每个房间的平均价格 (room_id)

+-------+---------+-----------+-------+--------+---------------------------+---------------------------+
| id    | room_id | dayofweek | price | source | created_at                | updated_at                |
+-------+---------+-----------+-------+--------+---------------------------+---------------------------+
| 87936 | 2517    | 0         | 14.58 | 1      | 2010-02-22 17:47:14 +0100 | 2010-02-22 17:47:14 +0100 |
| 87937 | 2517    | 1         | 14.58 | 1      | 2010-02-22 17:47:14 +0100 | 2010-02-22 17:47:14 +0100 |
| 87938 | 2517    | 2         | 14.52 | 1      | 2010-02-22 17:47:14 +0100 | 2010-02-22 17:47:14 +0100 |
| 87939 | 2517    | 3         | 14.52 | 1      | 2010-02-22 17:47:14 +0100 | 2010-02-22 17:47:14 +0100 |
| 87940 | 2517    | 4         | 14.52 | 1      | 2010-02-22 17:47:15 +0100 | 2010-02-22 17:47:15 +0100 |
| 87941 | 2517    | 5         | 14.4  | 1      | 2010-02-22 17:47:15 +0100 | 2010-02-22 17:47:15 +0100 |
| 87942 | 2517    | 6         | 14.63 | 1      | 2010-02-22 17:47:15 +0100 | 2010-02-22 17:47:15 +0100 |
+-------+---------+-----------+-------+--------+---------------------------+---------------------------+

和一个表称为“Availables”，具有特定日期的费率

+--------+-------+-------+------------+---------+---------------------------+---------------------------+--------+
| id     | price | spots | bookdate   | room_id | created_at                | updated_at                | source |
+--------+-------+-------+------------+---------+---------------------------+---------------------------+--------+
| 221389 | 14.3  | 1     | 2010-03-01 | 2517    | 2010-02-21 22:31:06 +0100 | 2010-02-21 22:31:06 +0100 | 1      |
| 221390 | 14.3  | 1     | 2010-03-02 | 2517    | 2010-02-21 22:31:06 +0100 | 2010-02-21 22:31:06 +0100 | 1      |
| 221391 | 14.3  | 1     | 2010-03-03 | 2517    | 2010-02-21 22:31:06 +0100 | 2010-02-21 22:31:06 +0100 | 1      |
| 221392 | 14.3  | 1     | 2010-03-04 | 2517    | 2010-02-21 22:31:06 +0100 | 2010-02-22 17:47:19 +0100 | 1      |
| 221393 |       | 0     | 2010-03-05 | 2517    | 2010-02-21 22:31:06 +0100 | 2010-02-22 17:47:19 +0100 | 1      |
| 221394 |       | 0     | 2010-03-06 | 2517    | 2010-02-21 22:31:06 +0100 | 2010-02-22 17:47:19 +0100 | 1      |
| 228185 |       | 0     | 2010-03-07 | 2517    | 2010-02-22 17:47:19 +0100 | 2010-02-22 17:47:19 +0100 | 1      |
| 228186 | 14.3  | 1     | 2010-03-08 | 2517    | 2010-02-22 17:47:19 +0100 | 2010-02-22 17:47:19 +0100 | 1      |
| 228187 | 14.3  | 1     | 2010-03-09 | 2517    | 2010-02-22 17:47:19 +0100 | 2010-02-22 17:47:19 +0100 | 1      |
| 228188 | 14.3  | 1     | 2010-03-10 | 2517    | 2010-02-22 17:47:19 +0100 | 2010-02-22 17:47:19 +0100 | 1      |
+--------+-------+-------+------------+---------+---------------------------+---------------------------+--------+

目前我正在使用两个单独的搜索结果，特定日期范围的当前 Availables 数据要么存在，要么不存在。如果没有，我将使用仅使用 Room_rate 平均值的备份查询。

我想在有可用价格的地方使用“Availables”，但如果价格不可用或可能根本没有记录，则以某种方式加入 Room_rates 来填补空白。

我怎样才能做到这一点？

Answer 1

COALESCE 函数可以与联接相结合来满足您的需求：

SELECT COALESCE(a.price, rr.price) 
  FROM Availables AS a
  FULL OUTER JOIN room_rates AS rr
    ON a.room_id = rr.room_id
  WHERE a.room_id=[id]
    AND a.bookdate=[date]
    AND rr.dayofweek=[dayofweek]

您需要在适当的列上建立索引才能提高性能。您还需要比较执行计划针对替代方案（例如 Bruno 建议的 UNION）的声明，看看连接是否值得。

Answer 2

连接的情况并非如此。连接会将一个表中的数据与另一个表中的数据进行匹配。例如，为了匹配 room 和 room_rates，您将使用联接。但您不想匹配“Available”和“Room_rates”。您想要从“可用”中获取房价，或者（如果在“可用”中找不到）从 Room_rates 中获取房价。

因此，您确实必须首先对“Available”运行查询，然后在需要时对“Room_rates”运行查询。

您可以获得查询返回的第一个结果，如下所示：

select price from Availables where room_id=[id] and bookdate=[date]
union
select price from room_rates where room_id=[id] and dayofweek=[day of week]

但我真的不建议这样做，因为即使不需要，也会运行第二个“选择”。因此，最好仅在需要时运行第一个选择，然后运行第二个选择。