Please explain how this SQL statement selects all column1 values.
请解释此SQL语句如何选择所有column1值。
So I have parameter values A, B, and *
有参数值A B和*
If I pass @brand = '*' then this statement returns all values in column1:
如果我传递@brand = '*'，则此语句返回列n1中的所有值:
SELECT *
FROM TABLE
WHERE (column1 = @brand OR @brand = '*')
I'm thinking, when @brand = '*' then it assigns @brand as TRUE, so whatever is in column1 will be true, so that's why it returns all values in column1
我在想，当@brand = '*'时，它将@brand赋值为TRUE，所以列n1中的任何值都为TRUE，这就是为什么它返回column1中的所有值
If I pass A then it will only return A values, if B then B values.
如果我传递A，那么它只会返回A值，如果B那么B值。
2 个解决方案
#1
3
Your assumption is correct. It evaluates the clauses in your WHERE statement based on the easiest paths, sequentially.
你的假设是正确的。它根据最简单的路径顺序计算WHERE语句中的子句。
So, because there is an OR, it first evaluates the @brand ='*' clause; if that yields TRUE, the WHERE clause is satisfied and no further comparisons are made.
因此，由于存在OR，它首先计算@brand ='*'子句;如果结果为真，那么WHERE子句就满足了，没有进一步的比较。
If it yields FALSE, then it evaluates the other clause in the expression: column1 = @brand.
如果生成FALSE，则计算表达式中的另一个子句:column1 = @brand。
However, I should note that this is not the correct way to do it in SQL, because SQL Server does not guarantee short-circuiting nor the execution order of logical clauses. It just happens to work that way in your case, but it is not guaranteed. You should change the WHERE clause to look like this:
但是，我要注意的是，在SQL中这样做并不正确，因为SQL Server不保证短路，也不保证逻辑子句的执行顺序。在你的情况下，它恰好是这样工作的，但这并不能保证。你应该把WHERE子句改为:
((@brand = '*') OR (@brand <> '*' AND column1 = @brand))
#2
1
How does this SQL statement work?
这个SQL语句是如何工作的?
Are you asking about logically?
你问的是逻辑问题吗?
SQL uses three valued logic. Each individual predicate can evaluate as either true, false, or unknown.
SQL使用三值逻辑。每个单独的谓词可以作为真、假或未知的值。
You need to consider the truth table for OR in your example to see how to combine the predicates to get the overall result.
您需要考虑示例中的真值表，以了解如何组合谓词以获得总体结果。
In the context of a WHERE clause the overall predicate must evaluate to true for the row to be returned in the result.
在WHERE子句的上下文中，对于结果中要返回的行，整体谓词必须求值为true。
The truth table above shows that it is sufficient to know that either one of the predicates is true. So if the first one evaluates to true there is no need to evaluate the second. This is known as short circuit evaluation.
上面的真值表表明，只要知道其中一个谓词为真就足够了。因此，如果第一个算为真，就不需要对第二个求值。这就是所谓的短路评估。
SQL Server does not guarantee either order of evaluation or that it will use short circuit evaluation.
SQL Server不保证计算顺序，也不保证使用短路计算。
An example
一个例子
DECLARE @Table TABLE (
column1 VARCHAR(50) PRIMARY KEY);
INSERT INTO @Table
VALUES ('brand1'),
('brand2'),
('brand3')
DECLARE @brand VARCHAR(50) = 'brand1'
SELECT *
FROM @Table
WHERE ( column1 = @brand
OR @brand = '*' );
The execution plan shows the following
执行计划显示以下内容
The entire table is scanned and the predicate evaluated on each row. SQL Server may or may not evaluate the conditions in the order shown and may or may not use short circuit evaluation. This information is not exposed to us.
扫描整个表，并对每一行计算谓词。SQL Server可以按显示的顺序评估条件，也可以或不可以使用短路评估。我们不知道这些信息。
A single row is returned - the first one.
返回一行——第一行。
Efficiency
效率
The semantics of the query above are just
上面查询的语义只是
SELECT *
FROM @Table
WHERE column1= 'brand1'
This ought to be possible to evaluate with a simple lookup against the index supporting the primary key - not scanning the whole table.
这应该可以通过对支持主键的索引进行简单查找来评估，而不是扫描整个表。
From SQL Server 2008 onwards you can use the following
从SQL Server 2008开始，您可以使用以下命令
DECLARE @brand VARCHAR(50) = 'brand1'
SELECT *
FROM @Table
WHERE (column1 = @brand OR @brand = '*')
OPTION (RECOMPILE)
This recompiles the statement just before executing it and does not cache the execution plan. This means that the plan can take account of the specific value of @brand passed.
这将在执行之前重新编译语句，并且不缓存执行计划。这意味着计划可以考虑通过的@brand的具体价值。
Now the comparison of @brand = '*' is done at compile time and determined to be false and the predicate simplified to column1 = 'brand1' - allowing an index seek to that specific value.
现在，对@brand = '*'的比较在编译时完成，并确定为false，谓词简化为column1 = 'brand1'——允许索引查找特定的值。
#1
3
Your assumption is correct. It evaluates the clauses in your WHERE statement based on the easiest paths, sequentially.
你的假设是正确的。它根据最简单的路径顺序计算WHERE语句中的子句。
So, because there is an OR, it first evaluates the @brand ='*' clause; if that yields TRUE, the WHERE clause is satisfied and no further comparisons are made.
因此，由于存在OR，它首先计算@brand ='*'子句;如果结果为真，那么WHERE子句就满足了，没有进一步的比较。
If it yields FALSE, then it evaluates the other clause in the expression: column1 = @brand.
如果生成FALSE，则计算表达式中的另一个子句:column1 = @brand。
However, I should note that this is not the correct way to do it in SQL, because SQL Server does not guarantee short-circuiting nor the execution order of logical clauses. It just happens to work that way in your case, but it is not guaranteed. You should change the WHERE clause to look like this:
但是，我要注意的是，在SQL中这样做并不正确，因为SQL Server不保证短路，也不保证逻辑子句的执行顺序。在你的情况下，它恰好是这样工作的，但这并不能保证。你应该把WHERE子句改为:
((@brand = '*') OR (@brand <> '*' AND column1 = @brand))
#2
1
How does this SQL statement work?
这个SQL语句是如何工作的?
Are you asking about logically?
你问的是逻辑问题吗?
SQL uses three valued logic. Each individual predicate can evaluate as either true, false, or unknown.
SQL使用三值逻辑。每个单独的谓词可以作为真、假或未知的值。
You need to consider the truth table for OR in your example to see how to combine the predicates to get the overall result.
您需要考虑示例中的真值表，以了解如何组合谓词以获得总体结果。
In the context of a WHERE clause the overall predicate must evaluate to true for the row to be returned in the result.
在WHERE子句的上下文中，对于结果中要返回的行，整体谓词必须求值为true。
The truth table above shows that it is sufficient to know that either one of the predicates is true. So if the first one evaluates to true there is no need to evaluate the second. This is known as short circuit evaluation.
上面的真值表表明，只要知道其中一个谓词为真就足够了。因此，如果第一个算为真，就不需要对第二个求值。这就是所谓的短路评估。
SQL Server does not guarantee either order of evaluation or that it will use short circuit evaluation.
SQL Server不保证计算顺序，也不保证使用短路计算。
An example
一个例子
DECLARE @Table TABLE (
column1 VARCHAR(50) PRIMARY KEY);
INSERT INTO @Table
VALUES ('brand1'),
('brand2'),
('brand3')
DECLARE @brand VARCHAR(50) = 'brand1'
SELECT *
FROM @Table
WHERE ( column1 = @brand
OR @brand = '*' );
The execution plan shows the following
执行计划显示以下内容
The entire table is scanned and the predicate evaluated on each row. SQL Server may or may not evaluate the conditions in the order shown and may or may not use short circuit evaluation. This information is not exposed to us.
扫描整个表，并对每一行计算谓词。SQL Server可以按显示的顺序评估条件，也可以或不可以使用短路评估。我们不知道这些信息。
A single row is returned - the first one.
返回一行——第一行。
Efficiency
效率
The semantics of the query above are just
上面查询的语义只是
SELECT *
FROM @Table
WHERE column1= 'brand1'
This ought to be possible to evaluate with a simple lookup against the index supporting the primary key - not scanning the whole table.
这应该可以通过对支持主键的索引进行简单查找来评估，而不是扫描整个表。
From SQL Server 2008 onwards you can use the following
从SQL Server 2008开始，您可以使用以下命令
DECLARE @brand VARCHAR(50) = 'brand1'
SELECT *
FROM @Table
WHERE (column1 = @brand OR @brand = '*')
OPTION (RECOMPILE)
This recompiles the statement just before executing it and does not cache the execution plan. This means that the plan can take account of the specific value of @brand passed.
这将在执行之前重新编译语句，并且不缓存执行计划。这意味着计划可以考虑通过的@brand的具体价值。
Now the comparison of @brand = '*' is done at compile time and determined to be false and the predicate simplified to column1 = 'brand1' - allowing an index seek to that specific value.
现在，对@brand = '*'的比较在编译时完成，并确定为false，谓词简化为column1 = 'brand1'——允许索引查找特定的值。