SELECT 
  a.country_code, 
  a.code, 
  b.state 
FROM 
  cscart_states as a 
  LEFT JOIN cscart_state_descriptions as b ON b.state_id = a.state_id 
  AND b.lang_code = 'en' 
WHERE 
  a.status = 'A' 
ORDER BY 
  a.country_code, 
  b.state

Query time 0.00615

JSON explain

{
  "query_block": {
    "select_id": 1,
    "filesort": {
      "sort_key": "a.country_code, b.state",
      "temporary_table": {
        "nested_loop": [
          {
            "table": {
              "table_name": "a",
              "access_type": "ALL",
              "rows": 667,
              "filtered": 100,
              "attached_condition": "a.`status` = 'A'"
            }
          },
          {
            "table": {
              "table_name": "b",
              "access_type": "eq_ref",
              "possible_keys": ["PRIMARY"],
              "key": "PRIMARY",
              "key_length": "9",
              "used_key_parts": ["state_id", "lang_code"],
              "ref": ["u358358020_main.a.state_id", "const"],
              "rows": 1,
              "filtered": 100,
              "attached_condition": "trigcond(b.lang_code = 'en')"
            }
          }
        ]
      }
    }
  }
}

Result

country_code code state
AM AG Aragatsotn oblast
AM AR Ararat oblast
AM AV Armavir oblast
AM GR Gegharkunik oblast
AM KT Kotayk oblast
AM LO Lori oblast
AM SH Shirak oblast
AM SU Syunik oblast
AM TV Tavush oblast
AM VD Vayots Dzor oblast
AM ER Yerevan
AU ACT Australian Capital Territory
AU NSW New South Wales
AU NT Northern Territory
AU QLD Queensland
AU SA South Australia
AU TAS Tasmania
AU VIC Victoria
AU WA Western Australia
BG SF Sofia
BY BR Brest Region
BY HM City of Minsk
BY HO Gomel Region
BY HR Grodno Region
BY MI Minsk Region
BY MA Mogilev Region
BY VI Vitebsk Region
CA AB Alberta
CA BC British Columbia
CA MB Manitoba
CA NB New Brunswick
CA NL Newfoundland and Labrador
CA NT Northwest Territories
CA NS Nova Scotia
CA NU Nunavut
CA ON Ontario
CA PE Prince Edward Island
CA QC Quebec
CA SK Saskatchewan
CA YT Yukon
CH AR Appenzell Rhodes-Extérieures
CH AI Appenzell Rhodes-Intérieures
CH AG Argovie
CH BL Bâle-Campagne
CH BS Bâle-Ville
CH BE Berne
CH FR Fribourg
CH GE Genève
CH GL Glaris
CH GR Grisons
CH JU Jura
CH LU Lucerne
CH NE Neuchâtel
CH NW Nidwald
CH OW Obwald
CH SG Saint-Gall
CH SH Schaffhouse
CH SZ Schwytz
CH SO Soleure
CH TI Tessin
CH TG Thurgovie
CH UR Uri
CH VS Valais
CH VD Vaud
CH ZG Zoug
CH ZH Zurich
DE BAW Baden-Württemberg
DE BAY Bayern
DE BER Berlin
DE BRG Branderburg
DE BRE Bremen
DE HAM Hamburg
DE HES Hessen
DE MEC Mecklenburg-Vorpommern
DE NDS Niedersachsen
DE NRW Nordrhein-Westfalen
DE RHE Rheinland-Pfalz
DE SAR Saarland
DE SAS Sachsen
DE SAC Sachsen-Anhalt
DE SCN Schleswig-Holstein
DE THE Thüringen
ES C A Coruña
ES VI Álava
ES AB Albacete
ES A Alicante
ES AL Almería
ES O Asturias
ES AV Ávila
ES BA Badajoz
ES PM Baleares
ES B Barcelona
ES BU Burgos
ES CC Cáceres
ES CA Cádiz
ES S Cantabria
ES CS Castellón
ES CE Ceuta
ES CR Ciudad Real
ES CO Córdoba
ES CU Cuenca
ES GI Girona
ES GR Granada
ES GU Guadalajara
ES SS Guipúzcoa
ES H Huelva
ES HU Huesca
ES J Jaén
ES LO La Rioja
ES GC Las Palmas
ES LE León
ES L Lleida
ES LU Lugo
ES M Madrid
ES MA Málaga
ES ML Melilla
ES MU Murcia
ES NA Navarra
ES OR Ourense
ES P Palencia
ES PO Pontevedra
ES SA Salamanca
ES TF Santa Cruz de Tenerife
ES SG Segovia
ES SE Sevilla
ES SO Soria
ES T Tarragona
ES TE Teruel
ES TO Toledo
ES V Valencia
ES VA Valladolid
ES BI Vizcaya
ES ZA Zamora
ES Z Zaragoza
FR 01 Ain
FR 02 Aisne
FR 03 Allier
FR 04 Alpes-de-Haute-Provence
FR 06 Alpes-Maritimes
FR 07 Ardèche
FR 08 Ardennes
FR 09 Ariège
FR 10 Aube
FR 11 Aude
FR 12 Aveyron
FR 67 Bas-Rhin
FR 13 Bouches-du-Rhône
FR 14 Calvados
FR 15 Cantal
FR 16 Charente
FR 17 Charente-Maritime
FR 18 Cher
FR 19 Corrèze
FR 2A Corse-du-Sud
FR 21 Côte-d'Or
FR 22 Côtes-d'Armor
FR 23 Creuse
FR 79 Deux-Sèvres
FR 24 Dordogne
FR 25 Doubs
FR 26 Drôme
FR 91 Essonne
FR 27 Eure
FR 28 Eure-et-Loir
FR 29 Finistère
FR 30 Gard
FR 32 Gers
FR 33 Gironde
FR 68 Haut-Rhin
FR 2B Haute-Corse
FR 31 Haute-Garonne
FR 43 Haute-Loire
FR 52 Haute-Marne
FR 70 Haute-Saône
FR 74 Haute-Savoie
FR 87 Haute-Vienne
FR 05 Hautes-Alpes
FR 65 Hautes-Pyrénées
FR 92 Hauts-de-Seine
FR 34 Hérault
FR 35 Ille-et-Vilaine
FR 36 Indre
FR 37 Indre-et-Loire
FR 38 Isère
FR 39 Jura
FR 40 Landes
FR 41 Loir-et-Cher
FR 42 Loire
FR 44 Loire-Atlantique
FR 45 Loiret
FR 46 Lot
FR 47 Lot-et-Garonne
FR 48 Lozère
FR 49 Maine-et-Loire
FR 50 Manche
FR 51 Marne
FR 53 Mayenne
FR 54 Meurthe-et-Moselle
FR 55 Meuse
FR 56 Morbihan
FR 57 Moselle
FR 58 Nièvre
FR 59 Nord
FR 60 Oise
FR 61 Orne
FR 75 Paris
FR 62 Pas-de-Calais
FR 63 Puy-de-Dôme
FR 64 Pyrénées-Atlantiques
FR 66 Pyrénées-Orientales
FR 69 Rhône
FR 71 Saône-et-Loire
FR 72 Sarthe
FR 73 Savoie
FR 77 Seine-et-Marne
FR 76 Seine-Maritime
FR 93 Seine-Saint-Denis
FR 80 Somme
FR 81 Tarn
FR 82 Tarn-et-Garonne
FR 90 Territoire de Belfort
FR 95 Val-d'Oise
FR 94 Val-de-Marne
FR 83 Var
FR 84 Vaucluse
FR 85 Vendée
FR 86 Vienne
FR 88 Vosges
FR 89 Yonne
FR 78 Yvelines
GB ABN Aberdeen
GB ABNS Aberdeenshire
GB ANG Anglesey
GB AGS Angus
GB ANN Antrim and Newtownabbey
GB AND Ards and North Down
GB ARY Argyll and Bute
GB ABC Armagh City, Banbridge and Craigavon
GB AVN Avon
GB BAN Banffshire
GB BEDS Bedfordshire
GB BFS Belfast City
GB BERKS Berkshire
GB BEW Berwickshire
GB BLA Blaenau Gwent
GB BRI Bridgend
GB BSTL Bristol
GB BUCKS Buckinghamshire
GB CAE Caerphilly
GB CAI Caithness
GB CAMBS Cambridgeshire
GB CDF Cardiff
GB CARM Carmarthenshire
GB CCG Causeway Coast and Glens
GB CDGN Ceredigion
GB CHES Cheshire
GB CLACK Clackmannanshire
GB CLV Cleveland
GB CWD Clwyd
GB CON Conwy
GB CORN Cornwall
GB ANT County Antrim
GB ARM County Armagh
GB DOW County Down
GB FER County Fermanagh
GB LDY County Londonderry
GB TYR County Tyrone
GB CMA Cumbria
GB DNBG Denbighshire
GB DERBY Derbyshire
GB DRS Derry and Strabane
GB DVN Devon
GB DOR Dorset
GB DGL Dumfries and Galloway
GB DFS Dumfries-shire
GB DUND Dundee
GB DHM Durham
GB DFD Dyfed
GB ARYE East Ayrshire
GB DUNBE East Dunbartonshire
GB LOTE East Lothian
GB RENE East Renfrewshire
GB ERYS East Riding of Yorkshire
GB SXE East Sussex
GB EDIN Edinburgh
GB ESX Essex
GB FALK Falkirk
GB FMO Fermanagh and Omagh
GB FFE Fife
GB FLINT Flintshire
GB GLAS Glasgow
GB GLOS Gloucestershire
GB LDN Greater London
GB MCH Greater Manchester
GB GDD Gwynedd
GB HANTS Hampshire
GB HWR Herefordshire
GB HERTS Hertfordshire
GB HLD Highlands
GB HUM Humberside
GB IVER Inverclyde
GB INV Inverness-shire
GB IOW Isle of Wight
GB IOS Isles of Scilly
GB KNT Kent
GB KCD Kincardineshire
GB LANCS Lancashire
GB LEICS Leicestershire
GB LINCS Lincolnshire
GB LBC Lisburn and Castlereagh
GB MER Merionethshire
GB MSY Merseyside
GB MERT Merthyr Tydfil
GB MEA Mid and East Antrim
GB MUL Mid-Ulster
GB MDX Middlesex
GB MLOT Midlothian
GB MMOUTH Monmouthshire
GB MORAY Moray
GB NAI Nairnshire
GB NPRTAL Neath Port Talbot
GB NEWPT Newport
GB NMD Newry, Mourne and Down
GB NOR Norfolk
GB ARYN North Ayrshire
GB LANN North Lanarkshire
GB YSN North Yorkshire
GB NHM Northamptonshire
GB NLD Northumberland
GB NOT Nottinghamshire
GB ORK Orkney Islands
GB OFE Oxfordshire
GB PEE Peebles-shire
GB PEM Pembrokeshire
GB PERTH Perth and Kinross
GB PWS Powys
GB REN Renfrewshire
GB RHON Rhondda Cynon Taff
GB ROX Roxburghshire
GB RUT Rutland
GB BOR Scottish Borders
GB SEL Selkirkshire
GB SHET Shetland Islands
GB SPE Shropshire
GB SOM Somerset
GB ARYS South Ayrshire
GB LANS South Lanarkshire
GB SYK South Yorkshire
GB SFD Staffordshire
GB STIR Stirling
GB STI Stirlingshire
GB SFK Suffolk
GB SRY Surrey
GB SUT Sutherland
GB SWAN Swansea
GB TORF Torfaen
GB TWR Tyne and Wear
GB VGLAM Vale of Glamorgan
GB WARKS Warwickshire
GB WDUN West Dunbartonshire
GB WLOT West Lothian
GB WMD West Midlands
GB SXW West Sussex
GB YSW West Yorkshire
GB WIL Western Isles
GB WIG Wigtownshire
GB WLT Wiltshire
GB WORCS Worcestershire
GB WRX Wrexham
GB YKS Yorkshire
GR ΑΧΑ Achaea
GR ΑΙΤ Aetolia-Acarnania
GR ΑΡΚ Arcadia
GR ΑΡΓ Argolis
GR ΑΡΤ Arta
GR ΑΤΤ Attica
GR ΒΟΙ Boeotia
GR ΚΕΦ Cephalonia
GR ΧΑΛ Chalkidiki
GR ΧΑΝ Chania
GR ΧΙΟ Chios
GR ΚΕΡ Corfu
GR ΚΟΡ Corinthia
GR ΚΥΚ Cyclades
GR ΔΩΔ Dodecanese
GR ΔΡΑ Drama
GR ΗΛΕ Elis
GR ΕΥΒ Euboea
GR ΕΒΡ Evros
GR ΕΥΡ Evrytania
GR ΦΛΩ Florina
GR ΓΡΕ Grevena
GR ΗΡΑ Heraklion
GR ΗΜΑ Imathia
GR ΙΩΑ Ioannina
GR ΚΑΡ Karditsa
GR ΚΑΣ Kastoria
GR ΚΑΒ Kavala
GR ΚΙΛ Kilkis
GR ΚΟΖ Kozani
GR ΛΑΚ Laconia
GR ΛΑΡ Larissa
GR ΛΑΣ Lasithi
GR ΛΕΥ Lefkada
GR ΛΕΣ Lesbos
GR ΜΑΓ Magnesia
GR ΜΕΣ Messinia
GR ΠΕΛ Pella
GR ΦΩΚ Phocis
GR ΦΘΙ Phthiotis
GR ΠΙΕ Pieria
GR ΠΡΕ Preveza
GR ΡΕΘ Rethymno
GR ΡΟΔ Rhodope
GR ΣΑΜ Samos
GR ΣΕΡ Serres
GR ΘΣΠ Thesprotia
GR ΘΕΣ Thessaloniki
GR ΤΡΙ Trikala
GR ΞΑΝ Xanthi
GR ΖΑΚ Zakynthos
IE CW Carlow
IE CN Cavan
IE CE Clare
IE CO Cork
IE DL Donegal
IE D Dublin
IE G Galway
IE KY Kerry
IE KE Kildare
IE KK Kilkenny
IE LS Laois
IE LM Leitrim
IE LK Limerick
IE LD Longford
IE LH Louth
IE MO Mayo
IE MH Meath
IE MN Monaghan
IE OY Offaly
IE RN Roscommon
IE SO Sligo
IE TA Tipperary
IE WD Waterford
IE WH Westmeath
IE WX Wexford
IE WW Wicklow
IN UP Uttar Pradesh
IT 65 Abruzzo
IT 23 Aosta Valley
IT 75 Apulia
IT 77 Basilicata
IT 78 Calabria
IT 72 Campania
IT 45 Emilia-Romagna
IT 36 Friuli Venezia Giulia
IT 62 Lazio
IT 42 Liguria
IT 25 Lombardy
IT 57 Marche
IT 67 Molise
IT 21 Piedmont
IT 88 Sardinia
IT 82 Sicily
IT 32 Trentino-South Tyrol
IT 52 Tuscany
IT 55 Umbria
IT 34 Veneto
KG B Batkenskaya oblast
KG GB Bishkek
KG C Chuyskaya oblast
KG J Dzhalal-Abadskaya oblast
KG Y Issyk-Kulskaya oblast
KG N Narynskaya oblast
KG O Oshskaya oblast
KG T Talasskaya oblast
KZ AKM Akmolinskaya oblast
KZ AKT Aktyubinskaya oblast
KZ ALM Almatinskaya oblast
KZ ALA Almaty
KZ AST Astana
KZ ATY Atyrauskaya oblast
KZ KAR Karagandinskaya oblast
KZ KUS Kostanayskaya oblast
KZ KZY Kyzylordinskaya oblast
KZ MAN Mangistauskaya oblast
KZ PAV Pavlodarskaya oblast
KZ SEV Severo-Kazakhstanskaya oblast
KZ VOS Vostochno-Kazakhstanskaya oblast
KZ YUZ Yuzhno-Kazakhstankaya oblast
KZ ZAP Zapadno-Kazakhstanskaya oblast
KZ ZHA Zhambylskaya oblast
NL DR Drenthe
NL FL Flevoland
NL FR Friesland
NL GE Gelderland
NL GR Groningen
NL LI Limburg
NL NB Noord Brabant
NL NH Noord Holland
NL OV Overijssel
NL UT Utrecht
NL ZE Zeeland
NL ZH Zuid Holland
RU ALT Altajskij kraj
RU AMU Amurskaja oblast'
RU ARK Arhangel'skaja oblast'
RU AST Astrahanskaja oblast'
RU BEL Belgorodskaja oblast'
RU BRY Brjanskaja oblast'
RU CE Chechenskaja respublika
RU CHE Cheljabinskaja oblast'
RU CHU Chukotskij avtonomnyj okrug
RU CU Chuvashskaja Respublika
RU YEV Evrejskaja avtonomnaja oblast'
RU KHA Habarovskij kraj
RU KHM Hanty-Mansijskij avtonomnyj okrug - Jugra
RU IRK Irkutskaja oblast'
RU IVA Ivanovskaja oblast'
RU YAN Jamalo-Neneckij avtonomnyj okrug
RU YAR Jaroslavskaja oblast'
RU KB Kabardino-Balkarskaja Respublika
RU KGD Kaliningradskaja oblast'
RU KLU Kaluzhskaja oblast'
RU KAM Kamchatskiy kraj
RU KC Karachaevo-Cherkesskaja respublika
RU KEM Kemerovskaja oblast'
RU KIR Kirovskaja oblast'
RU KOS Kostromskaja oblast'
RU KDA Krasnodarskij kraj
RU KYA Krasnojarskij kraj
RU KGN Kurganskaja oblast'
RU KRS Kurskaja oblast'
RU LEN Leningradskaja oblast'
RU LIP Lipeckaja oblast'
RU MAG Magadanskaja oblast'
RU MOS Moskovskaja oblast'
RU MOW Moskva
RU MUR Murmanskaja oblast'
RU NEN Neneckij avtonomnyj okrug
RU NIZ Nizhegorodskaja oblast'
RU NGR Novgorodskaja oblast'
RU NVS Novosibirskaja oblast'
RU OMS Omskaja oblast'
RU ORE Orenburgskaja oblast'
RU ORL Orlovskaja oblast'
RU PNZ Penzenskaja oblast'
RU PER Permskij kraj
RU PRI Primorskij kraj
RU PSK Pskovskaja oblast'
RU AD Respublika Adygeja
RU AL Respublika Altaj
RU BA Respublika Bashkortostan
RU BU Respublika Burjatija
RU DA Respublika Dagestan
RU KK Respublika Hakasija
RU IN Respublika Ingushetija
RU KL Respublika Kalmykija
RU KR Respublika Karelija
RU KO Respublika Komi
RU ME Respublika Marij Jel
RU MO Respublika Mordovija
RU SA Respublika Saha (Jakutija)
RU SE Respublika Severnaja Osetija-Alanija
RU TA Respublika Tatarstan
RU TY Respublika Tyva
RU RYA Rjazanskaja oblast'
RU ROS Rostovskaja oblast'
RU SAK Sahalinskaja oblast'
RU SAM Samarskaja oblast'
RU SPE Sankt-Peterburg
RU SAR Saratovskaja oblast'
RU SMO Smolenskaja oblast'
RU STA Stavropol'skij kraj
RU SVE Sverdlovskaja oblast'
RU TAM Tambovskaja oblast'
RU TYU Tjumenskaja oblast'
RU TOM Tomskaja oblast'
RU TUL Tul'skaja oblast'
RU TVE Tverskaja oblast'
RU UD Udmurtskaja Respublika
RU ULY Ul'janovskaja oblast'
RU VLA Vladimirskaja oblast'
RU VGG Volgogradskaja oblast'
RU VLG Vologodskaja oblast'
RU VOR Voronezhskaja oblast'
RU ZAB Zabaykal'skiy kraj
UA 71 Cherkaska oblast
UA 74 Chernihivska oblast
UA 77 Chernivetska oblast
UA 12 Dnipropetrovska oblast
UA 14 Donetska oblast
UA 26 Ivano-Frankivska oblast
UA 63 Kharkivska oblast
UA 65 Khersonska oblast
UA 68 Khmelnytska oblast
UA 35 Kirovohradska oblast
UA 30 Kyiv
UA 32 Kyivska oblast
UA 09 Luhanska oblast
UA 46 Lvivska oblast
UA 48 Nykolaivska oblast
UA 51 Odeska oblast
UA 53 Poltavska oblast
UA 56 Rovnenska oblast
UA 59 Sumska oblast
UA 61 Ternopilska oblast
UA 05 Vinnytska oblast
UA 07 Volynska oblast
UA 21 Zakarpatska oblast
UA 23 Zaporizka oblast
UA 18 Zhytomyrska oblast
US AL Alabama
US AK Alaska
US AZ Arizona
US AR Arkansas
US CA California
US CO Colorado
US CT Connecticut
US DE Delaware
US DC District of Columbia
US FL Florida
US GA Georgia
US GU Guam
US HI Hawaii
US ID Idaho
US IL Illinois
US IN Indiana
US IA Iowa
US KS Kansas
US KY Kentucky
US LA Louisiana
US ME Maine
US MD Maryland
US MA Massachusetts
US MI Michigan
US MN Minnesota
US MS Mississippi
US MO Missouri
US MT Montana
US NE Nebraska
US NV Nevada
US NH New Hampshire
US NJ New Jersey
US NM New Mexico
US NY New York
US NC North Carolina
US ND North Dakota
US MP Northern Mariana Islands
US OH Ohio
US OK Oklahoma
US OR Oregon
US PA Pennsylvania
US PR Puerto Rico
US RI Rhode Island
US SC South Carolina
US SD South Dakota
US TN Tennessee
US TX Texas
US UT Utah
US VT Vermont
US VI Virgin Islands
US VA Virginia
US WA Washington
US WV West Virginia
US WI Wisconsin
US WY Wyoming