Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
695 views
in Technique[技术] by (71.8m points)

sql server - SQL do inner join if condition met

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:

@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):

LEFT JOIN systemTable ON systemTable.param2=myTable.param2
WHERE 
   ((@SystemMerge=1 AND systemTable.param2 is not null) 
   OR
   (@SystemMerge=0 OR @SystemMerge is null))
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...