|=--------------------------------------------------=|
  _________________  .____     ___________________
 /   _____/\_____  \ |    |    \_   _____/\______ \
 \_____  \  /  / \  \|    |     |    __)_  |    |  \
 /        \/   \_/.  \    |___  |        \ |    `   \
/_______  /\_____\ \_/_______ \/_______  //_______  /
        \/        \__>       \/        \/         \/


|=--------------------------------------------------=|
. |03.08 - Joins                                     .
|=--------------------------------------------------=|

  In previous part we create foreign constraint. When
added pragma  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 as 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.

  This query will give us following results:

    user_name    no_text
  -----------  ------------
  user1        Hello World!
  user1        Notes no. 2
  user1        Notes no. 5
  user1        Notes no. 8
  user2        Notes no. 3
  user2        Notes no. 6
  user3        Notes no. 1
  user3        Notes no. 4
  user3        Notes no. 7

  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;
  
  We will get following result:

    user_name    no_text
  -----------  ---------
  user4

  We found user without note. And that's user4.

  That's it for this part.

|=--------------------------------------------------=|
; v  | Thanks Linux and Vim | buy me a coffee   | o |;
|=--------------------------------------------------=|