Using Inner, Left in Right Join in SQL

Select database queries with Inner, Left and Right Join represent an important part of SQL syntax and come in very handy when you want to make more advanced queries.

Simple queries were described in previous lesson

  SQL and showing data from two tables

In this lesson we will learn how to make queries using Inner, Left in Right Join.

For the purpose of this lesson we will again use tables my_table and country.

To see the complete structure of database please see the lesson mentioned above.

So far we have learned that we can get data from two tables if we use select and match the fields in both tables.

SELECT *

FROM my_table AS a, country AS b

WHERE a.id_country = b.id

Instead of this we can also use INNER JOIN which is just another syntax for the example above. The logic behind this is the same and that is why also the end result will be the same.

SELECT *

FROM my_table AS a
INNER JOIN country AS b
ON a.id_country = b.id

Of course we can add additional conditions at the end of the SQL statement also in this case by using keyword WHERE.

SELECT * FROM
my_table AS a
INNER JOIN country AS b
ON a.id_country = b.id
WHERE b.name = 'Japan'

In the example above we are matching  a.id_country = b.id. 

The problem here is that if there is no match, there will be no returned data.

If we want to see the data from first table even if there is no match in second table, we need LEFT JOIN.

SELECT * FROM
my_table AS a
LEFT JOIN country AS b 

ON  a.id_country = b.id

This will return all the records from my_table. If there will be no match for field id, we will still get the records, just missing values will be empty (NULL).

Similar logic also applies to RIGHT JOIN. 

In this case the query will return all the records from second table even if there is no match in the first table.

SELECT * FROM

my_table AS a
RIGHT JOIN country AS b 

ON  a.id_country = b.id

The main idea behind this is that by using LEFT and RIGHT JOIN we can make sure that we will always get all the data.