Sandbox

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 = 'state_field_required_only_for_countries_with_regions'

Query time 0.00013

Explain
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	cscart_ult_language_values	system	PRIMARY				0	const row not found
1	SIMPLE	lang	const	PRIMARY	PRIMARY	392	const,const	1

Result

value
The "State" field is required for all countries with states or provinces. If you don't have states for the customer's country, then the field will be required at checkout only if it is required in the customer's profile.