MySQL execute statement returns 'NULL' Could you please advise why this query returns 'NULL' on BASH shell? Basically I need to script it so could you please also advise what's the best practices to execute queries in BASH script?

MySQL CLI

mysql&gt; SELECT CONCAT(mail_name,"@",name) AS email_address,accounts.password 
     &gt; FROM mail 
     &gt; LEFT JOIN domains ON domains.id=mail.dom_id
     &gt; LEFT JOIN accounts ON accounts.id=mail.account_id LIMIT 2;
+-------------------------------------+----------+
| email_address                       | password |
+-------------------------------------+----------+
| an@example1.com                     | password1| 
| an@example2.com                     | password2|
+-------------------------------------+----------+
2 rows in set (0.00 sec)


BASH Shell

# mysql test -e "SELECT CONCAT(mail_name,"@",name) AS email_address,accounts.password FROM mail LEFT JOIN domains ON domains.id=mail.dom_id LEFT JOIN accounts ON accounts.id=mail.account_id LIMIT 2;"
+---------------+----------+
| email_address | password |
+---------------+----------+
| NULL          | password1| 
| NULL          | password2|    | 
+---------------+----------+
Whenever you are trying to concatenate a string with NULL, NULL is always returned, and for some reason @ is interpreted as NULL in MySQL. That is why the query returns NULL.

Since you wrap the string in the shell with " and you use it inside the query, the ones inside the query will be removed (literally, it is 3 strings that is being passed to MySQL).

You need to escape the quotation marks inside the query.

Like this:

# mysql test -e "SELECT CONCAT(mail_name,\"@\",name) AS email_address,accounts.password FROM mail LEFT JOIN domains ON domains.id=mail.dom_id LEFT JOIN accounts ON accounts.id=mail.account_id LIMIT 2;"


or 

# mysql test -e "SELECT CONCAT(mail_name,'@',name) AS email_address,accounts.password FROM mail LEFT JOIN domains ON domains.id=mail.dom_id LEFT JOIN accounts ON accounts.id=mail.account_id LIMIT 2;"