SQL: inner, left and self join examples

SQL joins relates data from two tables based on one or more columns, there are several types of joins; in this article, I will show you the following types

• inner join
• left join
• self join

In the examples, I will use SQLite it’s a free file-based database, that can be installed very easily in all windows, Linux, and macOS operating systems but I will not cover this topic since it differs in every operating system.

Create the database

Now that you have installed SQLite you can create the database$ sqlite3 data.db

It will drop you to the sqlite3 shell, create the person table using the following queryCREATE TABLE person(
 "id" TEXT,
 "sex" TEXT,
 "birth" INT
);

Now create the weight table using this queryCREATE TABLE weight(
 "id" TEXT,
 "weight" TEXT
);

to exit the SQLite shell enter.exit

Import CSV files to the database

Create the two following csv files

weight.csvid,weight
1,100
2,200
3,300

and the person.csvid,sex,birth
1,M,1990
2,M,1995
4,F,1993

Import the CSV files$ sqlite3 data.db ".mode csv" ".import weight.csv weight" ".exit"
$ sqlite3 data.db ".mode csv" ".import person.csv person" ".exit"

Working with JOINS

Now we have created the database and tables we can start working with the JOIN statements

Enter the sqlite3 shell$ sqlite3 data.db

Let’s examine the weight tableselect * from weight;
1|100
2|200
3|300

And the person’s tableselect * from person;id|sex|birth
1|M|1990
2|M|1995
4|F|1993

inner join

The inner join returns rows that have matching values in both tables.

Example:

In this example, the matching values are the person.id and the weight id, and in our select statement we ask for columns from both tablesselect person.id, weight.weight, person.sex, person.birth from person inner join weight on person.id = weight.id;1|100|M|1990
2|200|M|1995

left join

the left join returns all rows from the left table and the matched rows from the right table.

Example:

In this example, the left join returns all rows from the left table and the matched rows from the right table, as a left table we consider the table in the from keyword, and as the right table the table after the join keyword. As you might have noticed record “4” exists in our results but of course, it does not have a weight value because this id exists only in the “left” table. and not in the weight table on the “right”.select person.id, weight.weight, person.sex, person.birth from person left join weight on person.id = weight.id;id||sex|birth
1|100|M|1990
2|200|M|1995
4||F|1993

self join

A self-join is a regular join but the table is joined by itself

Example:

In this example, we have as a matching condition every birth value which matches with every other birth valueselect p1.id, p1.sex, p1.birth from person as p1 join person as p2 on p1.birth = p2.birth;
id|sex|birth
1|M|1990
2|M|1995
4|F|1993

You might wonder now how a self-join query can be useful, a self join, let’s see this with an exampleselect p1.id, p2.id, p1.birth,p2.birth from person as p1 join person as p2 on p1.birth+3 = p2.birth;
1|4|1990|1993

In this example, we limited the results with every birth value that can be equal to another birth value if we add 3 and we asked to get their ids, as you can see using a self-join can be powerful with such tricks.

I hope you found this article useful to understand how the SQL joins work.