How to use CONCAT with +

First of all, BigQuery doesn't support CONCAT_WS. As I know, most closest syntax is below:

SELECT ARRAY_TO_STRING(['aaa', 'bbb', 'ccc'], ',') AS concat_str;

output:

aaa,bbb,ccc

And regarding when you have to use CONCAT() or ARRAY_TO_STRING() in BigQuery (as an alternative of CONCAT_WS), it highly depends on the logic your query tries to implement.

My opinion is that ARRAY_TO_STRING() seems to be suited for CSV-styled string and CONCAT would be preferred where strings are concatenated without a repetitive delimiter.

SQL dialects vary quite a lot in the way they handle text-string data, both in storage and in the functions (CONCAT among others) used to manipulate that text. So a hard-core StackOverflow answer would insist you put the SQL variant in your question tags.

But your situation is not quite that.

So, let's say, for example, that you wish to use SQL concatenation to write www.example.com. You could do

CONCAT('www', '.', 'example', '.', 'com')

or

CONCAT_WS('.', 'www', 'example', 'com')

As you can see, for that example CONCAT_WS is a little more concise. And, if your app logic is built to handle that dot-separated style of names, it respects that logic.

On the other hand an expression like

CONCAT('surname:', user_surname)

is easier to express with CONCAT.

So, the answer: the choice depends on the application's data organization.

Performance-wise, the difference between CONCAT and CONCAT_WS is too small to measure, and far smaller than many other parts of satisfying a typical SQL query.

Let say you want to combine both First_name and Last_name you could say

CONCAT(First_name,' ',Last_name) AS full_name