SQL and showing data from two tables

We will learn how we can write SQL command to show data from two tables and also specify some additional conditions to limit the amount of data returned.

Basic input for our query will be data introduced in lesson

  Learn to use select query in SQL

If you are still learning SQL I really recommend to see this lesson.

A new field id_country was added to the table my_table.

Now we create a new table country with fields id and name.

We enter a few test values into the table.


Main benefit of storing data into separate tables is that in case the name of the the country changes, it is enough to correct only one record in one table.

If the name of the country would be in the same table as other data, we would have to change all the records with that country.

Data from two tables can be joined by setting condition in SQL where we define that field from one table should be the same as the field from other table.

In our case these fields are id_country and id.

SQL command is shown below:

SELECT *
FROM my_table AS a, country AS b
WHERE a.id_country = b.id

As a result we now see fields from both tables joined into one table where values of id and id_country matches.

Of course we usually do not need all the fields and instead of * we type the fields that we want.

SELECT a.text, b.name
FROM my_table AS a, country AS b
WHERE a.id_country = b.id

By doing this we also reduce memory consumption and speed up the execution of the query.

As a result we now get only fields that we requested.

Usually we also do not need all the records from the table, only the records which satisfy certain condition.

In this case we can add additional conditions at the end of SQL command.

If we want to see only records with county Japan, we can use the following SQL command:

SELECT a.text, b.name
FROM my_table AS a, country AS b
WHERE a.id_country = b.id
AND b.name='Japan'

As a result we now see only records with country Japan.