I would say a variable created with CREATE VARIABLE is a connection-specific global variable, i.e. it is accessible by all SQL statements of the current connection and can be changed by all these statements. There can only be one variable of that particular name at any time on one connection.

One would usually drop that variable when it is no more used. When the connection is closed, the variable is dropped automatically.

In contrast, DECLARE creates a local variable with block scope, i.e. it is visible and accessible within its block. AFAIK no variable of that particular name can be used in inner blocks (in constrast to typical programming languages where a variable v in an inner block "hides" a variable v in an outer block). (Note: I haven't tested that behaviour myself). Otherwise, this is the common kind of variable to use in stored procedures and the like.

As to the usage:

I would recommend to use local variables whenever possible.

CREATE VARIABLE is useful (and sometimes the most fitting approach) when you have to change behaviour on a "global scope", usually in more advanced situations. It often works like some kind of option. Some examples:

• One can use such a variable to change the behaviour of many/all triggers - cf. this question.
• One could filter results based on a connection-specific values (say, to list only the customers of a particular sales person) by including a filter value in the variable and use that in the WHERE clause of all according queries.

Besides that, in my opinion a global varible in SQL is possibly as "dangerous" as in other programming languages and should be handled with according care.