如果满足条件，SQL会执行内连接

i want a good way to improve my sql code, i have to use inner join when condition is met. I am currently replicates the code:

我想要一个改善我的SQL代码的好方法，我必须在满足条件时使用内连接。我目前正在复制代码：

@SystemMerge bit

if (@SystemMerge=1)  
BEGIN
   SELECT
         .......
      FROM myTable
      INNER JOIN table ON table.param1=myTable.param1
      INNER JOIN systemTable on systemTable.param2=myTable.param2
   END
ELSE
   BEGIN
      SELECT
         .......
      FROM myTable
      INNER JOIN table ON table.param1=myTable.param1
   END

and i would like to do it in a way like this:

我想以这样的方式做到这一点：

@SystemMerge bit
BEGIN
   SELECT
      .......
   FROM myTable
   INNER JOIN table ON table.param1=myTable.param1
   ***//the next 4 lines is not working, but this pseudo of what i want:***
   if (@SystemMerge=1)  
   begin
      INNER JOIN systemTable on systemTable.param2=myTable.param2
   end

edit: the solution (thanks to @Damien_The_Unbeliever):

编辑：解决方案（感谢@Damien_The_Unbeliever）：

LEFT JOIN systemTable ON systemTable.param2=myTable.param2
WHERE 
   ((@SystemMerge=1 AND systemTable.param2 is not null) 
   OR
   (@SystemMerge=0 OR @SystemMerge is null))

3 个解决方案

#1

This should (approxmately) do the same thing:

这应该（近似地）做同样的事情：

SELECT
     .......
  FROM myTable
  INNER JOIN table ON table.param1=myTable.param1
  LEFT JOIN systemTable on systemTable.param2=myTable.param2 and @SystemMerge = 1
  WHERE (@SystemMerge = 0 OR systemTable.NonNullableColumn IS NOT NULL)

Of course, this also means that any other references to columns within systemTable must be written to expect such columns to be NULL.

当然，这也意味着必须编写对systemTable中的列的任何其他引用，以期望这些列为NULL。

#2

How about dynamic sql?

动态sql怎么样？

declare @sel varchar(max)

set @sel = ' SELECT
         .......
         FROM myTable
         INNER JOIN table ON table.param1=myTable.param1
        '

 if (@SystemMerge=1)  
   begin
     set @sel = @sel+'INNER JOIN systemTable on systemTable.param2=myTable.param2'
   end

exec(@sel)

#3

Simple way if I am right -

如果我是对的简单方法 -

SELECT
    .......
FROM myTable
INNER JOIN table ON table.param1 = myTable.param1
INNER JOIN systemTable on @SystemMerge = 0 or systemTable.param2=myTable.param2

#1