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

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Categories

Design Rules, Bugs

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

  Message Line Column
1 SA0116 : Consider using EXISTS,IN or JOIN when usage of = (SELECT * FROM ) and the subquery returns more than column. 2 11
2 SA0116 : Consider using EXISTS,IN or JOIN when usage of = (SELECT * FROM ) and the subquery returns more than column. 5 11
3 SA0116 : Consider using EXISTS,IN or JOIN when usage of = (SELECT * FROM ) and the subquery returns more than column. 5 48
See Also

Other Resources