SQLite - JOIN Statements

A join statement is used to query data across multiple related tables.

Now that we have created a relationship between two tables, we can now use both tables within a single SELECT statement to return related data. We can do this using a JOIN statement.

We will query our tables using the three main join types:

The Inner Join

An inner join is a join in which the values in the columns being joined are compared using a comparison operator. In the SQL standard, inner joins can be specified in either the FROM or WHERE clause.

For example, we could run a query to retrieve a list of all artists, and their albums:

Result:

ArtistName    AlbumName           
------------  --------------------
Iron Maiden   Killers             
Iron Maiden   Powerslave          
Iron Maiden   Somewhere in Time   
Joe Satriani  Surfing with the Ali
Joe Satriani  Flying in a Blue Dre
Joe Satriani  Black Swans and Worm
Mr Percival   Out of the Loop     
Primus        Suck on This        
Primus        Pork Soda           
Primus        Sailing the Seas of 
Strapping Yo  Heavy as a Really He
The Wiggles   Yummy Yummy         
The Wiggles   Big Red Car   

You might have noticed that the above columns aren't quite wide enough for our new data (some of the data is being truncated). To display all data we'll need to increase the column sizes again.

Something like .width 20 50 should suffice for now.

We could also use a WHERE clause to narrow the results down to a particular artist:

AlbumName           
--------------------
Yummy Yummy         
Big Red Car

However, the WHERE could just have easily been an AND to extend the ON clause:

The Left Outer Join

An outer join returns all rows from at least one of the tables or views mentioned in the FROM clause, as long as those rows meet any WHERE or HAVING search conditions.

With a left outer join, all rows are retrieved from the left table referenced.

So in the following example, all rows from the Artists table are returned, regardless of whether there's a related record in the Albums table.

Result:

ArtistName            AlbumName                                         
--------------------  --------------------------------------------------
Joe Satriani          Black Swans and Wormhole Wizards                  
Joe Satriani          Flying in a Blue Dream                            
Joe Satriani          Surfing with the Alien                            
Steve Vai                                                               
The Tea Party                                                           
Noiseworks                                                              
Wayne Jury                                                              
Mr Percival           Out of the Loop                                   
Iron Maiden           Killers                                           
Iron Maiden           Powerslave                                        
Iron Maiden           Somewhere in Time                                 
Atmasphere                                                              
Ian Moss                                                                
Magnum                                                                  
Strapping Young Lad   Heavy as a Really Heavy Thing                     
Slayer                                                                  
Primus                Pork Soda                                         
Primus                Sailing the Seas of Cheese                        
Primus                Suck on This                                      
Pat Metheny                                                             
Frank Gambale                                                           
Frank Zappa                                                             
The Wiggles           Big Red Car                                       
The Wiggles           Yummy Yummy                                       

The Cross Join

A cross join (that does not have a WHERE clause) results in the Cartesian product of the tables involved in the join. Therefore, the size of the result set is the number of rows in the first table multiplied by the number of rows in the second table.

Be careful with the cross join. It can return extremely large result sets!

Result (you'll probably need to scroll):

ArtistName            AlbumName                                         
--------------------  --------------------------------------------------
Atmasphere            Killers                                           
Atmasphere            Powerslave                                        
Atmasphere            Surfing with the Alien                            
Atmasphere            Heavy as a Really Heavy Thing                     
Atmasphere            Yummy Yummy                                       
Atmasphere            Out of the Loop                                   
Atmasphere            Suck on This                                      
Atmasphere            Pork Soda                                         
Atmasphere            Sailing the Seas of Cheese                        
Atmasphere            Flying in a Blue Dream                            
Atmasphere            Black Swans and Wormhole Wizards                  
Atmasphere            Somewhere in Time                                 
Atmasphere            Big Red Car                                       
Frank Gambale         Killers                                           
Frank Gambale         Powerslave                                        
Frank Gambale         Surfing with the Alien                            
Frank Gambale         Heavy as a Really Heavy Thing                     
Frank Gambale         Yummy Yummy                                       
Frank Gambale         Out of the Loop                                   
Frank Gambale         Suck on This                                      
Frank Gambale         Pork Soda                                         
Frank Gambale         Sailing the Seas of Cheese                        
Frank Gambale         Flying in a Blue Dream                            
Frank Gambale         Black Swans and Wormhole Wizards                  
Frank Gambale         Somewhere in Time                                 
Frank Gambale         Big Red Car                                       
Frank Zappa           Killers                                           
Frank Zappa           Powerslave                                        
Frank Zappa           Surfing with the Alien                            
Frank Zappa           Heavy as a Really Heavy Thing                     
Frank Zappa           Yummy Yummy                                       
Frank Zappa           Out of the Loop                                   
Frank Zappa           Suck on This                                      
Frank Zappa           Pork Soda                                         
Frank Zappa           Sailing the Seas of Cheese                        
Frank Zappa           Flying in a Blue Dream                            
Frank Zappa           Black Swans and Wormhole Wizards                  
Frank Zappa           Somewhere in Time                                 
Frank Zappa           Big Red Car                                       
Ian Moss              Killers                                           
Ian Moss              Powerslave                                        
Ian Moss              Surfing with the Alien                            
Ian Moss              Heavy as a Really Heavy Thing                     
Ian Moss              Yummy Yummy                                       
Ian Moss              Out of the Loop                                   
Ian Moss              Suck on This                                      
Ian Moss              Pork Soda                                         
Ian Moss              Sailing the Seas of Cheese                        
Ian Moss              Flying in a Blue Dream                            
Ian Moss              Black Swans and Wormhole Wizards                  
Ian Moss              Somewhere in Time                                 
Ian Moss              Big Red Car                                       
Iron Maiden           Killers                                           
Iron Maiden           Powerslave                                        
Iron Maiden           Surfing with the Alien                            
Iron Maiden           Heavy as a Really Heavy Thing                     
Iron Maiden           Yummy Yummy                                       
Iron Maiden           Out of the Loop                                   
Iron Maiden           Suck on This                                      
Iron Maiden           Pork Soda                                         
Iron Maiden           Sailing the Seas of Cheese                        
Iron Maiden           Flying in a Blue Dream                            
Iron Maiden           Black Swans and Wormhole Wizards                  
Iron Maiden           Somewhere in Time                                 
Iron Maiden           Big Red Car                                       
Joe Satriani          Killers                                           
Joe Satriani          Powerslave                                        
Joe Satriani          Surfing with the Alien                            
Joe Satriani          Heavy as a Really Heavy Thing                     
Joe Satriani          Yummy Yummy                                       
Joe Satriani          Out of the Loop                                   
Joe Satriani          Suck on This                                      
Joe Satriani          Pork Soda                                         
Joe Satriani          Sailing the Seas of Cheese                        
Joe Satriani          Flying in a Blue Dream                            
Joe Satriani          Black Swans and Wormhole Wizards                  
Joe Satriani          Somewhere in Time                                 
Joe Satriani          Big Red Car                                       
Magnum                Killers                                           
Magnum                Powerslave                                        
Magnum                Surfing with the Alien                            
Magnum                Heavy as a Really Heavy Thing                     
Magnum                Yummy Yummy                                       
Magnum                Out of the Loop                                   
Magnum                Suck on This                                      
Magnum                Pork Soda                                         
Magnum                Sailing the Seas of Cheese                        
Magnum                Flying in a Blue Dream                            
Magnum                Black Swans and Wormhole Wizards                  
Magnum                Somewhere in Time                                 
Magnum                Big Red Car                                       
Mr Percival           Killers                                           
Mr Percival           Powerslave                                        
Mr Percival           Surfing with the Alien                            
Mr Percival           Heavy as a Really Heavy Thing                     
Mr Percival           Yummy Yummy                                       
Mr Percival           Out of the Loop                                   
Mr Percival           Suck on This                                      
Mr Percival           Pork Soda                                         
Mr Percival           Sailing the Seas of Cheese                        
Mr Percival           Flying in a Blue Dream                            
Mr Percival           Black Swans and Wormhole Wizards                  
Mr Percival           Somewhere in Time                                 
Mr Percival           Big Red Car                                       
Noiseworks            Killers                                           
Noiseworks            Powerslave                                        
Noiseworks            Surfing with the Alien                            
Noiseworks            Heavy as a Really Heavy Thing                     
Noiseworks            Yummy Yummy                                       
Noiseworks            Out of the Loop                                   
Noiseworks            Suck on This                                      
Noiseworks            Pork Soda                                         
Noiseworks            Sailing the Seas of Cheese                        
Noiseworks            Flying in a Blue Dream                            
Noiseworks            Black Swans and Wormhole Wizards                  
Noiseworks            Somewhere in Time                                 
Noiseworks            Big Red Car                                       
Pat Metheny           Killers                                           
Pat Metheny           Powerslave                                        
Pat Metheny           Surfing with the Alien                            
Pat Metheny           Heavy as a Really Heavy Thing                     
Pat Metheny           Yummy Yummy                                       
Pat Metheny           Out of the Loop                                   
Pat Metheny           Suck on This                                      
Pat Metheny           Pork Soda                                         
Pat Metheny           Sailing the Seas of Cheese                        
Pat Metheny           Flying in a Blue Dream                            
Pat Metheny           Black Swans and Wormhole Wizards                  
Pat Metheny           Somewhere in Time                                 
Pat Metheny           Big Red Car                                       
Primus                Killers                                           
Primus                Powerslave                                        
Primus                Surfing with the Alien                            
Primus                Heavy as a Really Heavy Thing                     
Primus                Yummy Yummy                                       
Primus                Out of the Loop                                   
Primus                Suck on This                                      
Primus                Pork Soda                                         
Primus                Sailing the Seas of Cheese                        
Primus                Flying in a Blue Dream                            
Primus                Black Swans and Wormhole Wizards                  
Primus                Somewhere in Time                                 
Primus                Big Red Car                                       
Slayer                Killers                                           
Slayer                Powerslave                                        
Slayer                Surfing with the Alien                            
Slayer                Heavy as a Really Heavy Thing                     
Slayer                Yummy Yummy                                       
Slayer                Out of the Loop                                   
Slayer                Suck on This                                      
Slayer                Pork Soda                                         
Slayer                Sailing the Seas of Cheese                        
Slayer                Flying in a Blue Dream                            
Slayer                Black Swans and Wormhole Wizards                  
Slayer                Somewhere in Time                                 
Slayer                Big Red Car                                       
Steve Vai             Killers                                           
Steve Vai             Powerslave                                        
Steve Vai             Surfing with the Alien                            
Steve Vai             Heavy as a Really Heavy Thing                     
Steve Vai             Yummy Yummy                                       
Steve Vai             Out of the Loop                                   
Steve Vai             Suck on This                                      
Steve Vai             Pork Soda                                         
Steve Vai             Sailing the Seas of Cheese                        
Steve Vai             Flying in a Blue Dream                            
Steve Vai             Black Swans and Wormhole Wizards                  
Steve Vai             Somewhere in Time                                 
Steve Vai             Big Red Car                                       
Strapping Young Lad   Killers                                           
Strapping Young Lad   Powerslave                                        
Strapping Young Lad   Surfing with the Alien                            
Strapping Young Lad   Heavy as a Really Heavy Thing                     
Strapping Young Lad   Yummy Yummy                                       
Strapping Young Lad   Out of the Loop                                   
Strapping Young Lad   Suck on This                                      
Strapping Young Lad   Pork Soda                                         
Strapping Young Lad   Sailing the Seas of Cheese                        
Strapping Young Lad   Flying in a Blue Dream                            
Strapping Young Lad   Black Swans and Wormhole Wizards                  
Strapping Young Lad   Somewhere in Time                                 
Strapping Young Lad   Big Red Car                                       
The Tea Party         Killers                                           
The Tea Party         Powerslave                                        
The Tea Party         Surfing with the Alien                            
The Tea Party         Heavy as a Really Heavy Thing                     
The Tea Party         Yummy Yummy                                       
The Tea Party         Out of the Loop                                   
The Tea Party         Suck on This                                      
The Tea Party         Pork Soda                                         
The Tea Party         Sailing the Seas of Cheese                        
The Tea Party         Flying in a Blue Dream                            
The Tea Party         Black Swans and Wormhole Wizards                  
The Tea Party         Somewhere in Time                                 
The Tea Party         Big Red Car                                       
The Wiggles           Killers                                           
The Wiggles           Powerslave                                        
The Wiggles           Surfing with the Alien                            
The Wiggles           Heavy as a Really Heavy Thing                     
The Wiggles           Yummy Yummy                                       
The Wiggles           Out of the Loop                                   
The Wiggles           Suck on This                                      
The Wiggles           Pork Soda                                         
The Wiggles           Sailing the Seas of Cheese                        
The Wiggles           Flying in a Blue Dream                            
The Wiggles           Black Swans and Wormhole Wizards                  
The Wiggles           Somewhere in Time                                 
The Wiggles           Big Red Car                                       
Wayne Jury            Killers                                           
Wayne Jury            Powerslave                                        
Wayne Jury            Surfing with the Alien                            
Wayne Jury            Heavy as a Really Heavy Thing                     
Wayne Jury            Yummy Yummy                                       
Wayne Jury            Out of the Loop                                   
Wayne Jury            Suck on This                                      
Wayne Jury            Pork Soda                                         
Wayne Jury            Sailing the Seas of Cheese                        
Wayne Jury            Flying in a Blue Dream                            
Wayne Jury            Black Swans and Wormhole Wizards                  
Wayne Jury            Somewhere in Time                                 
Wayne Jury            Big Red Car 

However, if a WHERE clause is added, the cross join behaves as an inner join.

Other Join Types

The SQL standard also includes RIGHT OUTER JOIN and FULL OUTER JOIN, however, SQLite doesn't support those join types.