The concatenation operator in SQL is the double pipe (||). It joins two or more strings together. For example, 'Hello' || ' ' || 'World' results in 'Hello World'. The other operators are arithmetic: +, -, * for addition, subtraction, and multiplication respectively.