Sandbox

SELECT 
  lang.name, 
  IF(
    cscart_ult_language_values.value IS NULL, 
    lang.value, cscart_ult_language_values.value
  ) as value 
FROM 
  cscart_language_values lang 
  LEFT JOIN cscart_ult_language_values ON cscart_ult_language_values.name = lang.name 
  AND company_id = 1 
  AND cscart_ult_language_values.lang_code = lang.lang_code 
WHERE 
  lang.lang_code = 'en' 
  AND lang.name IN (
    'contact_information', 'text_multiprofile_notice', 
    'billing_address', 'shipping_address', 
    'shipping_address', 'billing_address', 
    'revert', 'delete_my_account_description', 
    'delete_my_account', 'delete_my_account', 
    'delete_my_account', 'usergroup', 
    'status', 'action', 'active', 'available', 
    'join', 'declined', 'join', 'pending', 
    'cancel', 'contact_information', 
    'text_multiprofile_notice', 'billing_address', 
    'shipping_address', 'shipping_address', 
    'billing_address', 'revert', 'delete_my_account_description', 
    'delete_my_account', 'delete_my_account', 
    'delete_my_account', 'usergroup', 
    'status', 'action', 'active', 'available', 
    'join', 'declined', 'join', 'pending', 
    'cancel'
  )

Query time 0.00129

JSON explain

{
  "query_block": {
    "select_id": 1,
    "table": {
      "table_name": "cscart_ult_language_values",
      "access_type": "system",
      "possible_keys": ["PRIMARY"],
      "rows": 0,
      "filtered": 0,
      "const_row_not_found": true
    },
    "table": {
      "table_name": "lang",
      "access_type": "range",
      "possible_keys": ["PRIMARY"],
      "key": "PRIMARY",
      "key_length": "392",
      "used_key_parts": ["lang_code", "name"],
      "rows": 16,
      "filtered": 100,
      "index_condition": "lang.lang_code = 'en' and lang.`name` in ('contact_information','text_multiprofile_notice','billing_address','shipping_address','shipping_address','billing_address','revert','delete_my_account_description','delete_my_account','delete_my_account','delete_my_account','usergroup','status','action','active','available','join','declined','join','pending','cancel','contact_information','text_multiprofile_notice','billing_address','shipping_address','shipping_address','billing_address','revert','delete_my_account_description','delete_my_account','delete_my_account','delete_my_account','usergroup','status','action','active','available','join','declined','join','pending','cancel')"
    }
  }
}

Result

name	value
action	Action
active	Active
available	Available
billing_address	Billing address
cancel	Cancel
contact_information	Contact information
declined	Declined
delete_my_account	Delete my account
delete_my_account_description	<p>We’ll deactivate your account and remove all your personal data from it. After that you won’t be able to sign in. However, you’ll be able to register a new account with the same email address in the future. Please note that we’ll need some time to process this request, so your account won’t be deactivated immediately. If you have any comments regarding your request, please leave them in the field below.</p>
join	Join
pending	Pending
revert	Revert
shipping_address	Shipping address
status	Status
text_multiprofile_notice	One user can have multiple profiles. For example, one for personal orders and another for office needs.
usergroup	User group