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

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

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.

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

Rule has no parameters.

The rule does not need Analysis Context or SQL Connection.

Design Rules

There is no additional info for this rule.

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

Other Resources