Concatenation of values can be done either by using the concatenation operator || or the CONCAT function.
Below is the syntax using concatenation operator:
SELECT col1 || col2 FROM table1;
If one or more of the values specified with concatenation operator are null, the resulting output is also null.
Example: Below select statement returns a null value if a person has no middle name in the person table.
SELECT first_name||' '||middle_name||' '||last_name FROM person;
Below is the syntax using CONCAT function:
SELECT CONCAT(col1,col2) FROM table1;
Even if one or more of the specified values are null, CONCAT function ignores the null values and outputs the concatenated value of the non-null values.
Example: Below select statement returns a person’s name even if a person has no middle name in the person table.
SELECT CONCAT(first_name,' ',middle_name,' ',last_name) FROM person;