Click or drag to resize

SA0116 : Consider using EXISTS,IN or JOIN when usage of = (SELECT * FROM ) and the subquery returns more than column

The topic describes the SA0116 analysis rule.

Message

Consider using EXISTS,IN or JOIN when usage of = (SELECT * FROM ) and the subquery returns more than column

Description

The rule checks for comparison to a result form a subquery which returns more than one column.

Performing a comparison to a subquery like '= (SELECT [Name] FROM Table)' is generally a risky pattern to use. If the subquery ever returns more than one column then an exception will be generated. Consider using an IN, EXISTS, OR JOIN pattern instead.

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters
NameDescriptionDefault Value
RuleSuppressMark

Suppress rule reporting a rule violation in case a comment containing the value of this parameter is found close to the source of violation.

IGNORE:SA0116

Remarks

The rule does not need Analysis Context or SQL Connection.

Categories

Design Rules

Additional Information

There is no additional info for this rule.

Example Test Script
SQL
1SELECT * FROM Table1
2WHERE Col1 = ( SELECT Col1,Col2 FROM Table2)
3
4SELECT t.* FROM Table1 t
5WHERE Col1 = ( SELECT * FROM Table2 t2) OR Col2 >= ( SELECT t2.* FROM Table2 t2)

Analysis Results

 MessageLineColumn
1SA0116 : Consider using EXISTS,IN or JOIN when usage of = (SELECT * FROM ) and the subquery returns more than column.211
2SA0116 : Consider using EXISTS,IN or JOIN when usage of = (SELECT * FROM ) and the subquery returns more than column.511
3SA0116 : Consider using EXISTS,IN or JOIN when usage of = (SELECT * FROM ) and the subquery returns more than column.548
See Also

Other Resources