Using CONCAT_WS to extract full name from first , middle and last name.
It also handles the case that the mid_name can be empty or null or even have multiple spaces!
SELECT id, email,
CASE WHEN mid_name IS NULL OR TRIM(mid_name) ='' THEN
CONCAT_WS( " ", first_name, last_name )
ELSE
CONCAT_WS( " ", first_name, mid_name, last_name )
END AS full_name
FROM USER;