SQL joins

I came across a great post about SQL joins. Below is the example I tried out on Oracle9i.

SQL> create table TableA (id integer, name varchar(20));
SQL> insert into TableA values (1, 'Pirate');
SQL> insert into TableA values (2, 'Monkey');
SQL> insert into TableA values (3, 'Ninja');
SQL> insert into TableA values (4, 'Spaghetti');
SQL>
SQL> create table TableB (id integer, name varchar(20));
SQL> insert into TableB values (1, 'Rutabaga');
SQL> insert into TableB values (2, 'Pirate');
SQL> insert into TableB values (3, 'Darth Vader');
SQL> insert into TableB values (4, 'Ninja');
SQL>
SQL> SELECT * FROM TableA
2 INNER JOIN TableB
3 ON TableA.name = TableB.name
4 order by TableA.id, TableB.id;

     ID NAME                         ID NAME                          
---------- -------------------- ---------- --------------------          
  1 Pirate                        2 Pirate                        
  3 Ninja                         4 Ninja                         

SQL>
SQL> SELECT * FROM TableA
2 FULL OUTER JOIN TableB
3 ON TableA.name = TableB.name
4 order by TableA.id, TableB.id;

       ID NAME                         ID NAME                            
---------- -------------------- ---------- --------------------            
    1 Pirate                        2 Pirate                          
    2 Monkey                                                          
    3 Ninja                         4 Ninja                           
    4 Spaghetti                                                       
                                    1 Rutabaga                        
                                    3 Darth Vader                     

SQL>
SQL> SELECT * FROM TableA
2 LEFT OUTER JOIN TableB
3 ON TableA.name = TableB.name
4 order by TableA.id, TableB.id;

       ID NAME                         ID NAME                            
---------- -------------------- ---------- --------------------            
    1 Pirate                        2 Pirate                          
    2 Monkey                                                          
    3 Ninja                         4 Ninja                           
    4 Spaghetti                                                       

SQL>
SQL> SELECT * FROM TableA
2 LEFT OUTER JOIN TableB
3 ON TableA.name = TableB.name
4 WHERE TableB.id IS null
5 order by TableA.id, TableB.id;

        ID NAME                         ID NAME                             
---------- -------------------- ---------- --------------------             
     2 Monkey                                                           
     4 Spaghetti                                                        

SQL>
SQL> SELECT * FROM TableA
2 FULL OUTER JOIN TableB
3 ON TableA.name = TableB.name
4 WHERE TableA.id is null
5 OR TableB.id is null
6 order by TableA.id, TableB.id;

       ID NAME                         ID NAME                             
---------- -------------------- ---------- --------------------             
     2 Monkey                                                           
     4 Spaghetti                                                        
                                     1 Rutabaga                         
                                     3 Darth Vader                      

SQL>
SQL> SELECT * FROM TableA
2 CROSS JOIN TableB;

       ID NAME                         ID NAME                             
---------- -------------------- ---------- --------------------             
     1 Pirate                        1 Rutabaga                         
     2 Monkey                        1                                  
     3 Ninja                         1                                  
     4 Spaghetti                     1                                  
     1 Pirate                        2 Pirate                           
     2 Monkey                        2                                  
     3 Ninja                         2                                  
     4 Spaghetti                     2                                  
     1 Pirate                        3 Darth Vader                      
     2 Monkey                        3                                  
     3 Ninja                         3                                  

    ID NAME                         ID NAME                             
---------- -------------------- ---------- --------------------             
     4 Spaghetti                     3 Darth Vader                      
     1 Pirate                        4 Ninja                            
     2 Monkey                        4                                  
     3 Ninja                         4                                  
     4 Spaghetti                     4

SQL>
SQL> drop table TableA;
SQL> drop table TableB;