Concat with Separator Function in mysql

MySQL also supports concat_ws function which allows us to concatenate two or more than two strings with a predefined separator. The syntax of the concat_ws function is:

CONCAT_WS(seperator,str1,str2,...)

The first parameter is the predefined separator you specified and the others are the string you want to concatenate. the result is the concatenating string with separator between each. For example, you can achieve the same result in the above example by using concat_ws function instead of concat function.

Here is example of using concat_ws to get address format of customers.
SELECT CONCAT_WS(char(13),
       CONCAT_WS(' ',contactLastname,contactFirstname),      addressLine1,
       addressLine2,
CONCAT_WS(' ',postalCode,city),
country,
       CONCAT_WS(char(13),'')
        ) AS Customer_Address
FROM customers
LIMIT 2



UNION OPERATOR

We use UNION operator in order to combine multiple SELECT statements. However, we also can use the WHERE clause instead of UNION to combine the return SELECT statements. We need to make our own decision when to use WHERE clause or when to use the UNION operator to combine the return SELECT statement. It is totally depending on the situation.

In order to use the UNION Operator, we need least two tables
All the query that use the UNION Operator must have the same columns, aggregate functions, and expressions
UNION operator is automatically removed the duplicate return rows
UNION ALL will return all the rows included the duplicate rows


Suppose you want to combine customers and employees infomation into one result set, you use the following query:
SELECT customerNumber id, contactLastname name
FROM customers
UNION
SELECT employeeNumber id,firstname name
FROM employees;


When using ORDER BY to sort the result with UNION, you have to use it in the last SQL SELECT statement. It would be the best to parenthesize all the SELECT statements and place ORDER BY at the end.

Suppose you want to sort the combination of employees and customers in the query above by nameandID in ascending order.
 (SELECT customerNumber id,contactLastname name
FROM customers)
UNION
(SELECT employeeNumber id,firstname name
FROM employees)
ORDER BY name,id;

What will be displayed in the output if we don’t use alias for each column in the SELECT statements? MySQL will use the column names of the first SELECT statement as the label of the output.


 (SELECT customerNumber, contactLastname
FROM customers)
UNION
(SELECT employeeNumber, firstname
FROM employees)
ORDER BY contactLastname, customerNumber;

MySQL also provides you another option to sort the result set based on column position in the ORDER BY clause as the following query:


 (SELECT customerNumber, contactLastname
FROM customers)
UNION
(SELECT employeeNumber,firstname
FROM employees)
ORDER BY 2, 1;