|=--------------------------------------------------=|
      _________________  .____     ___________________
     /   _____/\_____  \ |    |    \_   _____/\______ \
     \_____  \  /  / \  \|    |     |    __)_  |    |  \
     /        \/   \_/.  \    |___  |        \ |    `   \
    /_______  /\_____\ \_/_______ \/_______  //_______  /
            \/        \__>       \/        \/         \/
    
    
    |=--------------------------------------------------=|
    . |03.08 - Joins                                     .
    |=--------------------------------------------------=|
    
      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.
    
      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.
    
    |=--------------------------------------------------=|
    |=--------------------------------------------------=|