Country Codes

Country Codes is one of the five questions in the HackerRank SQL (Basic) certification test. The join is simple. Most people who fail it get the string joining wrong, because MySQL handles it differently from other databases.

What the question asks

A company has customers in many countries and wants to call all of them. Their phone numbers are stored without the country code. A second table maps each country to its code.

Return every customer’s id, name and full phone number, where the full number is a +, then the country code, then the phone number. Sort the result by customer_id.

For a customer in India with the number 9876543210, the expected value is +919876543210.

Tables

Solution

SELECT c.customer_id, c.name,
       CONCAT('+', cc.country_code, c.phone_number) AS phone_number
FROM customers c
LEFT JOIN country_codes cc ON cc.country = c.country
ORDER BY c.customer_id;

How it works

1. The join matches each customer’s country with the same country in country_codes, which gives us the code.
2. CONCAT() glues three pieces of text together: the + sign, the code and the number. No spaces are added unless you add them.
3. ORDER BY c.customer_id sorts the rows the way the question asks.

I used a LEFT JOIN so that every customer stays in the list even if their country is missing from country_codes. In the test, every customer’s country exists in the codes table, so a plain JOIN gives the same result and also passes.

Check the sample output in your version of the question. Almost every source, including a 2025 repo from someone who passed, uses no space: +919876543210. One transcription of the question says the format is “+CODE NUMBER” with a space, but its own sample output has no space. If your sample output shows a space, change the CONCAT to CONCAT('+', cc.country_code, ' ', c.phone_number).

Mistakes that fail the hidden tests

• Using + to join strings. That works in SQL Server, not in MySQL. In MySQL, '+' + cc.country_code + c.phone_number turns everything into numbers and adds them. In my test, India’s row came out as 9876543301, the code and the number added together.
• Using || to join strings. That works in Oracle, PostgreSQL and SQLite. In MySQL, || means logical OR by default, so every row came out as 1.
• Forgetting the plus sign. CONCAT(cc.country_code, c.phone_number) gives 919876543210, which does not match.
• Missing ORDER BY. The question asks for the output sorted by customer_id. In MySQL the rows often come back in id order anyway because of the primary key, but that is luck, not a rule. Write the ORDER BY.
• Converting the phone number to a number. The phone number is text. Casting it to an integer can drop leading zeros. Leave it as it is.

One more MySQL detail: if any piece passed to CONCAT() is NULL, the whole result is NULL. With the LEFT JOIN, a customer whose country is missing gets NULL as the phone number instead of a broken “+9876543210”. With an INNER JOIN, that customer is dropped. Neither case shows up in the test data, but it is good to know which one your query does.

Try it on sample data

These are my own rows. The customers are inserted out of id order on purpose, so the sort has something to do.

customers

country_codes

Output

I ran this in MySQL and SQLite and both gave these four rows. If you test in SQLite, use version 3.44 or newer, which added CONCAT().