基于Join关联两表查询：优化title_code参数条件的SQL需求

First off, let's spot the issue with your existing query: that OR (AMEDIAproject_TitleManagement_TitleDetails.title_code is not null) clause is breaking your intended logic. When you pass a value for @title_code, this OR condition will still pull every row where title_code isn't null—completely ignoring your parameter. That's definitely not what you want!

Your goal is clear:

• When no title_code parameter is provided (i.e., @title_code is NULL), return all rows where title_code has a value
• When a title_code parameter is provided, return only the rows matching that specific value

Here's a cleaned-up, optimized version of your query that meets these requirements:

SELECT * 
FROM AMEDIAproject_TitleManagement_TitleDetails td
FULL OUTER JOIN AMEDIAproject_TitleManagement_TitleOwner town
    ON td.title_owner_id = town.title_owner_id
WHERE 
    (@title_code IS NULL AND td.title_code IS NOT NULL)
    OR (td.title_code = @title_code)

Key improvements: #

• Table aliases: Using td and town for your tables makes the query shorter and easier to read—this is a standard best practice for SQL.
• Clear conditional logic: The WHERE clause explicitly handles both scenarios:
  1. When @title_code is NULL, we only keep rows where td.title_code isn't empty (matching your "return all title_code对应的数据" requirement)
  2. When @title_code has a value, we only keep rows that exactly match that value

If you prefer a more concise approach (using ANSI-standard SQL), you can use COALESCE to simplify the condition:

SELECT * 
FROM AMEDIAproject_TitleManagement_TitleDetails td
FULL OUTER JOIN AMEDIAproject_TitleManagement_TitleOwner town
    ON td.title_owner_id = town.title_owner_id
WHERE 
    td.title_code = COALESCE(@title_code, td.title_code)
    AND td.title_code IS NOT NULL

How this works: COALESCE(@title_code, td.title_code) returns the first non-null value. So:

• If @title_code is NULL, it uses td.title_code itself (so the condition becomes td.title_code = td.title_code, which is true for all non-null title_code rows)
• If @title_code has a value, it matches exactly against td.title_code

Just a quick note: If you're using SQL Server, you could also use ISNULL instead of COALESCE, but COALESCE is more portable across different database systems.

内容的提问来源于stack exchange，提问作者Nomi Ahmad