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.