Contents
What is the difference between SET and SELECT?
SET is used to assign a value to a variable and SELECT is used to assign a value or to select value from a variable/table/view etc. SELECT statement can assign values to multiple variables in a single go, as shown in above query. In this case, the SELECT statement performs better than SET.
Why we use SET in SQL?
Use the SET statement to assign a value that isn’t NULL to a declared variable. The SET statement that assigns a value to the variable returns a single value. When you initialize multiple variables, use a separate SET statement for each local variable.
What happens if a variable is not successfully populated?
If a variable is not successfully populated then behavior for SET and SELECT would be different. Failed assignment may be due to no result returned or any non-compatible value assigned to the variable. In this case, SELECT would preserve the previous value if any, where SET would assign NULL.
When to use set vs select for assigning variables?
While SELECT could accept multiple returned values. But after accepting multiple values through a SELECT command you have no way to track which value is present in the variable. The last value returned in the list will populate the variable.
When to use select and set in Java?
If you have to populate multiple variables, instead of using separate SET statements each time consider using SELECT for populating all variables in a single statement. This can be used for populating variables directly or by selecting values from database. Consider the following script comparing the use of SELECT and SET.
What’s the difference between set and select in SQL?
Another difference between assigning values to the SQL variables using the SET and SELECT statements, is when the subquery that is used to assign a value to the variable return no value. If the previously declared variable has no initial value, both the SET and SELECT statement will act in the same way, assigning NULL value to that variable.