SQL优化-根据列值连接不同的表 [英] SQL Optimization - Join different tables based on column value

问题描述

我有一个表，其中包含一个充当标志"的列，该列用于决定从哪个表中提取其他信息(即值1从表1中提取，值2从表2中提取，等等).通常，我只是使用索引/键加入表.但是，我可以连接的表包含可以标准化为单独表的信息，这使我陷入了使用列来确定要连接哪个表的情况.

I have a table that contains a column that acts as a "flag" which is used to decide which table to pull additional information from (i.e. the value 1 pulls from table1, 2 from table2, etc). Usually I would just join the table using indexes/keys. However the table that I could join contained information that could be normalized into separate tables which leaves me to this situation of using a column to decide which table to join.

这是我的问题，根据此列中产生的值，连接不同表的最有效方法是什么?

So here is my question, what is the most efficient way to join different tables based on the value produced in this column?

以下是我目前知道如何完成此任务的两种方法.我很确定它们都不是最佳解决方案:

Here are the two ways I know how to accomplish this task currently. I am pretty sure they are both not the optimal solution:

1. 从主表中提取信息(包含决定要连接的表的列值)，然后通过应用程序中的代码发送其他查询以获取其余信息.

1. Pull the information from my main table (containing the column value that decides which table to join), and then through code in my application send additional queries to get the rest of the information.

疯狂加入，返回每个表的列(即使未使用).然后，通过我的代码，忽略不需要的表的空值.

Go join crazy, return the columns of every table (even if unused). Then, through my code, ignore the nulls of the tables not needed.

推荐答案

我认为有可能:

create table a (id integer, flag boolean);
create table b (id integer, value_b varchar(30));
create table c (id integer, value_c varchar(30));

insert into a values (1, true), (2, false);
insert into b values (1, 'Val 1'), (2, 'Val 2');
insert into c values (1, 'C 1'), (2, 'C 2');

select a.id,
       case when a.flag then b.value_b else c.value_c end AS value
  from a
  left join b using (id)
  left join c using (id);

您可以尝试一下.

当然有局限性

• 列数是固定的，因此，如果应省略某些值，则应使用NULL；
• 您必须为每列写一个CASE ... END；
• 您应该提前了解所有联接表；
• 性能可能不是最好的.

• number of columns is fixed, so you should go for NULLs if some values should be omitted;
• you'll have to write a CASE ... END for each column;
• you should know all joined tables in advance;
• performance might not be the best.

这篇关于SQL优化-根据列值连接不同的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！