In previous part we create foreign constraint. When
added pragme the sqlite controlled the inserts for
validity and prevented us from inserting notes to
non-existing user. We added some data into the notes
table. Now we will examine queries that connects
these two tables.
There are several types of joins between tables.
The most basic are inner joins. Inner join will
search first table and according to filter will
select given records from the second table. When
there is a inner join specified with second table
then the second table is searched and records for
given records from 1st table will be selected.
Let's select all data from notes for given users.
select
u.user_name,
n.no_text
from users u
inner join notes n
on u.user_key = n.user_key
order by
u.user_key, n.no_key asc;
The inner join uses previously created relation
between user and notes table. We are using aliases
for the tables to simplify the notation. This is e.g.
notes n. When alias is specified for table in the
from clause then the alias can be used in the rest of
the SQL. This can simplify and shorten the notation
for the given SQL.
We will now examine the outer joins. Common outer
join is LEFT JOIN. That will use relationship of the
two tables. But this time it will provide so-called
outer join and when there are no records in the
joined table the row will display with columns from
joined tables empty. In databases there is special
term for empty cell - NULL.
In the preparation of the user's notes we didn't
specify any note for the user4. Let's see if we can
query the database to see if there is any user that
doesn't have any note stored.
select
u.user_name,
n.no_text
from users u
left join notes n
on u.user_key = n.user_key
where n.no_text is null
order by
u.user_key, n.no_key asc;
