Detailed explanation of the difference between SET and SELECT assignment in SQL

Recently, many SQL statements are written in the project, and variable assignment is often used, and I use SET and SELECT to achieve the effect.
What is the difference between the two? When and which one?
After online queries and personal exercises, the two are summarized as follows:
Suppose there are set variables:
Copy codeThe Code is as follows:
DECLARE @ VAR1 VARCHAR (1)
DECLARE @ VAR2 VARCHAR (2)

1. SELECT can assign values to multiple variables at the same time in a statement, while SET can assign values to only one variable at a time, as shown below:
Copy codeThe Code is as follows:
SELECT @ VAR1 = 'y', @ VAR2 = 'n'
-- To achieve the same effect, SET needs:
SET @ VAR1 = 'y'
SET @ VAR2 = 'n'
/*
Speaking of this, SQL built-in variables: @ ERROR and @ ROWCOUNT must be captured in an SQL statement. If you use set to obtain them in two sentences, the result is incomplete. In this case, select should be used to obtain the value.
*/

2. When the expression returns multiple values, an error occurs when you use SET, and SELECT takes the last value as follows:
Copy codeThe Code is as follows:
---- Assume that the Permission table has multiple IsRight records.
SELECT @ VAR1 = IsRight FROM Permission -- takes the last value

SET @ VAR1 = IsRight FROM Permission -- an error is returned.

3. When the expression has no return value, use SET to SET the variable value to NULL and use SELECT to maintain the variable value, as shown below:
Copy codeThe Code is as follows:
---- Assume that the Permission record is null.
SET @ VAR1 = 'Initiator'


SELECT @ VAR1 = IsRight FROM Permission -- at this time @ VAR1 is the 'Initiator'

SET @ VAR1 = (SELECT IsRight FROM Permission) -- @ VAR1 is NULL

4. If the scalar quantum query has no return value, SET and SELECT are both SET to NULL, as shown below:
Copy codeThe Code is as follows:
---- Assume that the Permission record is null.
SET @ VAR1 = 'Initiator'

SELECT @ VAR1 = (SELECT IsRight FROM Permission) -- @ VAR1 is NULL

SET @ VAR1 = (SELECT IsRight FROM Permission) -- @ VAR1 is NULL