Concatenation of values can be done either by using the concatenation operator || or the CONCAT function. Resulting output is the same in both scenarios.
If one or more of the values specified with concatenation operator or CONCAT function are null, the resulting output is also null.
Below is the syntax using concatenation operator:
SELECT col1 || col2 FROM table1;
Below is the syntax using CONCAT function:
SELECT CONCAT(col1,col2) FROM table1;
Concatenation operator can be used with any number of expressions. Whereas CONCAT function accepts only 2 values. CONCAT functions can be nested to concatenate multiple values.
Syntax to concatenate more than 2 expressions using concatenation operator:
SELECT col1 || col2|| col3|| col4 FROM table1;
Syntax to concatenate more than 2 expressions using CONCAT function:
SELECT CONCAT(col1,CONCAT(col2,CONCAT(col3,col4))) FROM table1;