SET versus SELECT when assigning variables?

Question

What are the differences between the SET and SELECT statements when assigning variables in T-SQL?

Answer 1

Differences between SET and SELECT while assigning the variables are as follows:

Want to learn SQL from scratch? Here's is the right video for you on SQL provided by Intellipaat

SET	SELECT
It’s ANSI standard for variable assignment.	Whereas, SELECT is Non-ANSI standard.
It can assign only one variable at a time.	Whereas, this can assign values to more than one variable at a time.
Set assigns a null value to the variables, when you assign from the query and the query returns no result. Example: DECLARE @CustomerID NCHAR(5) SET @CustomerID = 'XYZ' SET @CustomerID = (SELECT [CustomerID]                    FROM [dbo].[Customers]                    WHERE [CustomerID] = 'ABC') SELECT @CustomerID -– Returns NULL	When you assign from a query and the query returns no result. SELECT doesn’t make the assignment and hence it can’t change the value of the variable Example: DECLARE @CustomerID NCHAR(5) SET @CustomerID = 'XYZ' SELECT @CustomerID = [CustomerID] FROM [dbo].[Customers] WHERE [CustomerID] = 'ABC' SELECT @CustomerID –- Returns XYZ
When you assign from the query that returns more than one value, then SET results into an error Example: SET = (SELECT [CustomerID]        FROM [dbo].[Customers])   Msg 512, Level 16, State 1, Line 3 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.	When you assign from the query that returns more than one value, SELECT assigns the last value returned by the query and hides the fact that the query returned more than one row. Example: SELECT @CustomerID = [CustomerID] FROM [dbo].[Customers] -- No error generated