Question

write a query to join two tables​

Answer 1

Answer:

First, let’s quickly check what is the contents of these 3 tables.



We can notice two important things:

While each city has a related country, not all countries have related cities (Spain & Russia don’t have them)

Same stands for the customers. Each customer has the city_id value defined, but only 3 cities are being used (Berlin, Zagreb & New York)

Let’s first write down the query using INNER JOIN:

1

2

3

4

SELECT country.country_name_eng, city.city_name, customer.customer_name

FROM country

INNER JOIN city ON city.country_id = country.id

INNER JOIN customer ON customer.city_id = city.id;

The query result is shown in the picture below:



We have 7 counties and 6 cities in our database, but our query returns only 4 rows. That is the result of the fact we have only 4 customers in our database. Each of these 4 is related to its city and the city is related to the country. So, INNER JOIN eliminated all these countries and cities without customers. But how to include these in the result too?

To do that, we’ll use LEFT JOIN. We’ll simply replace all “INNER” with “LEFT” so our query is as follows:

1

2

3

4

SELECT country.country_name_eng, city.city_name, customer.customer_name

FROM country

LEFT JOIN city ON city.country_id = country.id

LEFT JOIN customer ON customer.city_id = city.id;

The result is shown in the picture below: