代码之家 › 专栏 › 技术社区 › Brett

我需要某种条件连接

left-join sql-server-2008 sql-server sql

Brett · 技术社区 · 14 年前

好吧,我知道有几篇文章讨论这个问题,但是我的问题不能通过连接上的条件WHERE语句来解决(常见的解决方案)。

我有三个 join 语句,根据查询参数,我可能需要运行这三个语句的任意组合。我的join语句非常昂贵,所以我只想在查询需要时进行join,而且我不准备编写一个7组合 IF..ELSE.. 实现这些组合的声明。

以下是迄今为止我使用的解决方案,但所有这些都不理想:

LEFT JOIN joinedTable jt
ON jt.someCol = someCol
WHERE jt.someCol = conditions
OR @neededJoin is null

(这太贵了,因为我正在执行联接,即使我不需要它,也不评估联接)

OUTER APPLY
(SELECT TOP(1) * FROM joinedTable jt 
WHERE jt.someCol = someCol
AND @neededjoin is null)

(这比总是留下来参加更贵)

SELECT @sql = @sql + ' INNER JOIN joinedTable jt ' +
             ' ON jt.someCol = someCol ' +
             ' WHERE (conditions...) '

(这一个是理想的,以及它现在是如何编写的,但我正在尝试将其从动态SQL转换为其他类型)。

任何想法或帮助都是伟大的!

编辑: 如果我采用动态SQL方法,我将尝试找出在构造查询时最有效的方法。考虑到我有三个可选条件,并且我需要所有这些条件的结果,我当前的查询执行如下操作:

IF condition one
SELECT from db
INNER JOIN condition one

UNION

IF condition two
SELECT from db
INNER JOIN condition two

UNION

IF condition three
SELECT from db
INNER JOIN condition three

我的非动态查询通过执行左联接来执行此任务:

SELECT from db
LEFT JOIN condition one
LEFT JOIN condition two
LEFT JOIN condition three
WHERE condition one is true
OR condition two is true
OR condition three is true

哪个更有意义?因为“select from db”语句中的所有代码都是相同的?联合条件似乎更有效,但我的查询很长,因为它……

谢谢!

6 回复 | 直到 14 年前

Jonathan Leffler 14 年前

动态SQL解决方案在大多数方面都是最好的;您正在尝试使用不同数量的联接运行不同的查询,而不需要重写查询来执行不同数量的联接,这在性能方面并不能很好地工作。

当我在大约90年代初做这种事情时,我使用的语言是i4gl,查询是使用其construct语句构建的。这用于生成WHERE子句的一部分,因此(基于用户输入),它生成的筛选条件可能如下所示:

a.column1 BETWEEN 1 AND 50 AND
b.column2 = 'ABCD' AND
c.column3 > 10

在那些日子里,我们没有现代的加入符号;我将不得不随机应变一点。通常有一个核心表(或一组核心表)始终是查询的一部分;也有一些表是查询的可选部分。在上面的示例中,我假设“c”是主表的别名。代码的工作方式是:

请注意,查询中引用了表“a”:
- 将“fulltablename as a”添加到FROM子句中
- 在WHERE子句中添加联接条件“和a.join1=c.join1”
请注意,表“B”已被引用…
- 将位添加到FROM子句和WHERE子句。
从选择列表(通常是固定的)、FROM子句和WHERE子句(有时也带有诸如GROUP BY、HAVING或ORDER BY之类的修饰)中组装SELECT语句。

同样的基本技术也应该在这里应用——但是细节上略有不同。

首先,您没有要分析的字符串;从其他情况下,您知道需要向查询中添加哪些表。所以,你仍然需要设计一些东西来组装它们,但是…

select子句及其select列表可能是固定的。它将标识查询中必须存在的表,因为值是从这些表中提取的。
FROM子句可能由一系列联接组成。
- 其中一部分是核心查询:
```
FROM CoreTable1 AS C1
JOIN CoreTable2 AS C2
     ON C1.JoinColumn = C2.JoinColumn
JOIN CoreTable3 AS M
     ON M.PrimaryKey = C1.ForeignKey
```
- 其他表格可根据需要添加:
```
JOIN AuxilliaryTable1 AS A
     ON M.ForeignKey1 = A.PrimaryKey
```
- 或者可以指定完整查询:
```
JOIN (SELECT RelevantColumn1, RelevantColumn2
        FROM AuxilliaryTable1
       WHERE Column1 BETWEEN 1 AND 50) AS A
```
- 在第一种情况下,您必须记住将WHERE条件添加到MAIN WHERE子句中,并信任DBMS优化器将条件移动到联接表中,如图所示。一个好的优化器会自动完成这一任务,而一个差的优化器可能不会。使用查询计划帮助您确定DBMS的能力。
为联接操作中未包含的任何表间条件和基于核心表的任何筛选条件添加WHERE子句。注意,我主要考虑的是额外的条件(和操作),而不是替代的条件(或操作),但是您可以处理,或者只要您小心地用括号将表达式括起来就行。
有时,您可能需要添加一些联接条件来将一个表连接到查询的核心——这并不奇怪。
添加任何分组依据、拥有或排序依据条款(或限制或任何其他装饰)。

注意,您需要对数据库模式和连接条件有一个很好的理解。基本上,这是用编程语言编写的,就像您必须考虑构造查询一样。只要你理解这一点和你的模式,就不会有任何无法克服的问题。

祝你好运。。。

gbn 14 年前

LEFT JOIN
joinedTable jt ON jt.someCol = someCol AND jt.someCol = conditions AND @neededjoin ...
...

或

LEFT JOIN
(
SELECT col1, someCol, col2 FROM joinedTable WHERE someCol = conditions AND @neededjoin ...
) jt ON jt.someCol = someCol
...

或

;WITH jtCTE AS
(SELECT col1, someCol, col2 FROM joinedTable WHERE someCol = conditions AND @neededjoin ...)
SELECT
...
LEFT JOIN
jtCTE ON jtCTE.someCol = someCol
...

老实说,除非使用文本,否则没有条件联接这样的构造。

如果它在SQL语句中,它将被评估…所以不要在SQL语句中使用动态SQL或if-else

HLGEM 14 年前

动态SQL解决方案通常是这些情况下最好的解决方案,但是如果您真的需要摆脱这种情况,那么一系列的if语句将在strood门廊中完成这项工作。这是一种痛苦,您必须编写更多的代码,但这将比在语句本身中使join成为条件更快。

A-K 14 年前

我将采用一种简单而直接的方法,如:

DECLARE @ret TABLE(...) ;

IF <coondition one> BEGIN ;
  INSERT INTO @ret() SELECT ...
END ;

IF <coondition two> BEGIN ;
  INSERT INTO @ret() SELECT ...
END ;

IF <coondition three> BEGIN ;
  INSERT INTO @ret() SELECT ...
END ;

SELECT DISTINCT ... FROM @ret ;

编辑:我建议使用一个表变量,而不是临时表,这样程序就不会在每次运行时重新编译。一般来说,三个更简单的插入比一个结合了这三个大型怪物查询的插入更有可能获得更好的执行计划。

然而,我们不能猜测刺激的表现。我们必须通过基准来确定它。然而,更简单的代码块对于可读性和可维护性更好。

ErikE Russ Cam 14 年前

试试这个:

LEFT JOIN joinedTable jt
   ON jt.someCol = someCol
   AND jt.someCol = conditions
   AND @neededJoin = 1 -- or whatever indicates join is needed

我想你会发现这是一个很好的表现,做你需要的。

更新

如果这不能提供我声称的性能,那么这可能是因为我上次使用联接到表来执行此操作。我需要的值可以来自3个表中的一个,基于2列,因此我构建了一个“连接映射”表,如下所示:

Col1  Col2 TableCode
  1     2    A
  1     4    A
  1     3    B
  1     5    B
  2     2    C
  2     5    C
  1     11   C

然后,

SELECT
   V.*,
   LookedUpValue =
      CASE M.TableCode
      WHEN 'A' THEN A.Value
      WHEN 'B' THEN B.Value
      WHEN 'C' THEN C.Value
      END
FROM
    ValueMaster V
    INNER JOIN JoinMap M ON V.Col1 = M.oOl1 AND V.Col2 = M.Col2
    LEFT JOIN TableA A ON M.TableCode = 'A'
    LEFT JOIN TableB B ON M.TableCode = 'B'
    LEFT JOIN TableC C ON M.TableCode = 'C'

这使我在查询这些表(大多数是几十或数亿行表)时获得了巨大的性能改进。

这就是为什么我问你是否真的得到了改进。当然,它会将一个连接抛出到执行计划中,并分配一些成本,但总的来说, 要做的工作要少得多 而不是一个不加区别地把三张桌子连在一起的计划 Coalesce() 找到正确的值。

如果您发现与动态SQL相比,用这种方式进行连接只需要5%的开销,但是如果不加区分的连接比动态SQL高100%,那么这样做可能是值得的,因为动态SQL的正确性、清晰性和简单性,所有这些都可能比一个小的改进更有价值(取决于您“当然在做。”

成本规模与行数是否也是另一个需要考虑的因素。如果即使有大量的数据,在一个每秒不运行几十次的查询上也只节省了200毫秒的CPU,那么使用它是一件很简单的事情。

我一直在强调这样一个事实,即我认为它的性能会很好,这是因为即使使用哈希匹配,它也没有任何要探测的行,或者也没有任何要创建哈希的行。与使用初始日志的WHERE子句或样式查询相比,哈希操作将提前停止。

John Fisher 14 年前

因为没有其他人提到这个,这里有一些您可以使用的东西(不是动态的)。如果语法看起来很奇怪,那是因为我在Oracle中测试了它。

基本上,您可以将联接的表转换为具有WHERE子句的子选择,如果条件不匹配,该子句将不返回任何内容。如果条件匹配,则子选择将返回该表的数据。case语句允许您选择在总体选择中返回的列。

with m as (select 1 Num, 'One' Txt from dual union select 2, 'Two' from dual union select 3, 'Three' from dual),
t1 as (select 1 Num from dual union select 11 from dual),
t2 as (select 2 Num from dual union select 22 from dual),
t3 as (select 3 Num from dual union select 33 from dual)
SELECT m.*
      ,CASE 1
         WHEN 1 THEN
          t1.Num
         WHEN 2 THEN
          t2.Num
         WHEN 3 THEN
          t3.Num
       END SelectedNum
  FROM m
  LEFT JOIN (SELECT * FROM t1 WHERE 1 = 1) t1 ON m.Num = t1.Num
  LEFT JOIN (SELECT * FROM t2 WHERE 1 = 2) t2 ON m.Num = t2.Num
  LEFT JOIN (SELECT * FROM t3 WHERE 1 = 3) t3 ON m.Num = t3.Num