SELECT 
  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 = 'newsletters.agreement_text_full_subscribe_to_newsletters'

Query time 0.00041

JSON explain

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "1.00"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "cscart_ult_language_values",
          "access_type": "system",
          "possible_keys": [
            "PRIMARY"
          ],
          "rows_examined_per_scan": 0,
          "rows_produced_per_join": 1,
          "filtered": "0.00",
          "const_row_not_found": true,
          "cost_info": {
            "read_cost": "0.00",
            "eval_cost": "0.10",
            "prefix_cost": "0.00",
            "data_read_per_join": "408"
          },
          "used_columns": [
            "lang_code",
            "name",
            "value",
            "company_id"
          ]
        }
      },
      {
        "table": {
          "table_name": "lang",
          "access_type": "const",
          "possible_keys": [
            "PRIMARY"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "lang_code",
            "name"
          ],
          "key_length": "392",
          "ref": [
            "const",
            "const"
          ],
          "rows_examined_per_scan": 1,
          "rows_produced_per_join": 1,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "0.00",
            "eval_cost": "0.10",
            "prefix_cost": "0.00",
            "data_read_per_join": "408"
          },
          "used_columns": [
            "lang_code",
            "name",
            "value"
          ]
        }
      }
    ]
  }
}

Result

value
<p>By subscribing to our newsletter, you authorise KOSHAL SOLUTIONS, S.L. to process your identification and contact details for the purpose of sending you information about our services, updates, technical content and invitations related to our solutions. The legal basis is your consent, which you may withdraw at any time by using the “unsubscribe” link included in each message or by contacting us. We do not use third-party analytics or advertising tools in connection with these communications. Your data will be retained while you remain subscribed and, thereafter, for the minimum period necessary to demonstrate compliance with GDPR. You may exercise your data-protection rights as explained in our Privacy Policy.</p>