Difference Between SET and SELECT

SELECT and SET statements are similar in many ways but there are some differences between them.

• SELECT has better performance over SET when used for assigning values to multiple variables at the same time.
• SET is ANSI Standard for value assignment to variables but SELECT is not an ANSI Standard for variable assignment.
• SET can be used to assign value to one variable at a time but SELECT can be used to assign values to multiple variables in a single SELECT statement.
• When an output of a query is used to assign values to a variable then SET Statement would fail and give an error if multiple rows are returned by the query but the SELECT statement would assign the last result of the query to the the variable.
• If the variable is initially assigned a value following is the behavior of variable assignment using SET and SELECT.

SET – Assigns null if the query does not return any rows.
SELECT – Retains the initially assigned value and does not assign null if the query does not return any rows.