|=--------------------------------------------------=|
. |03.10 - Set operations                            .
|=--------------------------------------------------=|

  To study  the set  operations first we  will create
new table as  exact duplicate of the  users table. We
will simply call it users2.

  create table users2 as select * from users;

  First we will  remove 2 rows for  the users2 table.
This will result in more illustrative examples.

  delete from users2
  where user_key in (3,4);

  To use the set operators  all used tables must have
corresponding column data types.

  First  we will  use UNION  ALL. This  set operation
will select all  rows from first table.  Then it will
select all rows from second table (or third, fourth).
The result  of this  query will then  concatenate all
the rows  together to  one result.  For us  to better
distinct between  the result  of both tables  we will
add name  of the  source table  as new  string column
named tab.

  select 'users' tab, u.* from users u
  union all
  select 'users2' tab, u2.* from users2 u2;

  The above query will give following results:

  tab       user_key  user_name    user_password
  ------  ----------  -----------  ---------------
  users            1  user1        pass
  users            2  user2        pass
  users            3  user3        pass
  users            4  user4        pass
  users2           1  user1        pass
  users2           2  user2        pass

  Now we  will insert one  row into the  users2 table
and will  try to select  all distinct rows  from both
first and second table.

  insert into users2 (
    user_key, user_name, user_password
  ) values (
    5,'users','pass'
  );

  To query  both tables and select  all distinct rows
we will use the UNION set operation. The query:

  select * from users
  union
  select * from users2;

  Will give us following result:

    user_key  user_name    user_password
  ----------  -----------  ---------------
           1  user1        pass
           2  user2        pass
           3  user3        pass
           4  user4        pass
           5  users        pass
  
  Another set operator is EXCEPT. This will query the
second table and will return rows not included in the
first table.

  select * from users2
  except 
  select * from users;

  The result  will be one  row that is in  the users2
table but  isn't present  in the users  table. That's
the row we inserted previously.

    user_key  user_name    user_password
  ----------  -----------  ---------------
           5  users        pass


  The last set operator we will examine is INTERSECT.
And  this operator  queries  first  table and  second
table and find the rows that's in both of the tables.
The query:

  select * from users
  intersect
  select * from users2;

  Will  result  in the  data  set  of rows  that  are
contained in both of the tables.

    user_key  user_name    user_password
  ----------  -----------  ---------------
           1  user1        pass
           2  user2        pass

  And that's it for this part.