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:

• Inner Join
• Left Outer Join
• Cross Join

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:

SELECT ArtistName, AlbumName 

FROM Artists AS a 

INNER JOIN Albums AS b 

 ON a.ArtistId = b.ArtistId

ORDER BY ArtistName;

Result:

ArtistName    AlbumName------------  --------------------Iron Maiden   KillersIron Maiden   PowerslaveIron Maiden   Somewhere in TimeJoe Satriani  Surfing with the AliJoe Satriani  Flying in a Blue DreJoe Satriani  Black Swans and WormMr Percival   Out of the LoopPrimus        Suck on ThisPrimus        Pork SodaPrimus        Sailing the Seas ofStrapping Yo  Heavy as a Really HeThe Wiggles   Yummy YummyThe 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:

SELECT b.AlbumName 

FROM Artists AS a 

INNER JOIN Albums AS b 

 ON a.ArtistId = b.ArtistId 

WHERE a.ArtistName = 'The Wiggles';

AlbumName--------------------Yummy YummyBig Red Car

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

SELECT b.AlbumName 

FROM Artists AS a 

INNER JOIN Albums AS b 

 ON a.ArtistId = b.ArtistId 

 AND a.ArtistName = 'The Wiggles';

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.

SELECT a.ArtistName, b.AlbumName 

FROM Artists AS a 

LEFT OUTER JOIN Albums AS b 

 ON a.ArtistId = b.ArtistId;

Result:

ArtistName            AlbumName--------------------  --------------------------------------------------Joe Satriani          Black Swans and Wormhole WizardsJoe Satriani          Flying in a Blue DreamJoe Satriani          Surfing with the AlienSteve VaiThe Tea PartyNoiseworksWayne JuryMr Percival           Out of the LoopIron Maiden           KillersIron Maiden           PowerslaveIron Maiden           Somewhere in TimeAtmasphereIan MossMagnumStrapping Young Lad   Heavy as a Really Heavy ThingSlayerPrimus                Pork SodaPrimus                Sailing the Seas of CheesePrimus                Suck on ThisPat MethenyFrank GambaleFrank ZappaThe Wiggles           Big Red CarThe 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!

SELECT ArtistName, AlbumName FROM Artists AS a 

CROSS JOIN Albums AS b

ORDER BY ArtistName;

Result (you'll probably need to scroll):

ArtistName            AlbumName--------------------  --------------------------------------------------Atmasphere            KillersAtmasphere            PowerslaveAtmasphere            Surfing with the AlienAtmasphere            Heavy as a Really Heavy ThingAtmasphere            Yummy YummyAtmasphere            Out of the LoopAtmasphere            Suck on ThisAtmasphere            Pork SodaAtmasphere            Sailing the Seas of CheeseAtmasphere            Flying in a Blue DreamAtmasphere            Black Swans and Wormhole WizardsAtmasphere            Somewhere in TimeAtmasphere            Big Red CarFrank Gambale         KillersFrank Gambale         PowerslaveFrank Gambale         Surfing with the AlienFrank Gambale         Heavy as a Really Heavy ThingFrank Gambale         Yummy YummyFrank Gambale         Out of the LoopFrank Gambale         Suck on ThisFrank Gambale         Pork SodaFrank Gambale         Sailing the Seas of CheeseFrank Gambale         Flying in a Blue DreamFrank Gambale         Black Swans and Wormhole WizardsFrank Gambale         Somewhere in TimeFrank Gambale         Big Red CarFrank Zappa           KillersFrank Zappa           PowerslaveFrank Zappa           Surfing with the AlienFrank Zappa           Heavy as a Really Heavy ThingFrank Zappa           Yummy YummyFrank Zappa           Out of the LoopFrank Zappa           Suck on ThisFrank Zappa           Pork SodaFrank Zappa           Sailing the Seas of CheeseFrank Zappa           Flying in a Blue DreamFrank Zappa           Black Swans and Wormhole WizardsFrank Zappa           Somewhere in TimeFrank Zappa           Big Red CarIan Moss              KillersIan Moss              PowerslaveIan Moss              Surfing with the AlienIan Moss              Heavy as a Really Heavy ThingIan Moss              Yummy YummyIan Moss              Out of the LoopIan Moss              Suck on ThisIan Moss              Pork SodaIan Moss              Sailing the Seas of CheeseIan Moss              Flying in a Blue DreamIan Moss              Black Swans and Wormhole WizardsIan Moss              Somewhere in TimeIan Moss              Big Red CarIron Maiden           KillersIron Maiden           PowerslaveIron Maiden           Surfing with the AlienIron Maiden           Heavy as a Really Heavy ThingIron Maiden           Yummy YummyIron Maiden           Out of the LoopIron Maiden           Suck on ThisIron Maiden           Pork SodaIron Maiden           Sailing the Seas of CheeseIron Maiden           Flying in a Blue DreamIron Maiden           Black Swans and Wormhole WizardsIron Maiden           Somewhere in TimeIron Maiden           Big Red CarJoe Satriani          KillersJoe Satriani          PowerslaveJoe Satriani          Surfing with the AlienJoe Satriani          Heavy as a Really Heavy ThingJoe Satriani          Yummy YummyJoe Satriani          Out of the LoopJoe Satriani          Suck on ThisJoe Satriani          Pork SodaJoe Satriani          Sailing the Seas of CheeseJoe Satriani          Flying in a Blue DreamJoe Satriani          Black Swans and Wormhole WizardsJoe Satriani          Somewhere in TimeJoe Satriani          Big Red CarMagnum                KillersMagnum                PowerslaveMagnum                Surfing with the AlienMagnum                Heavy as a Really Heavy ThingMagnum                Yummy YummyMagnum                Out of the LoopMagnum                Suck on ThisMagnum                Pork SodaMagnum                Sailing the Seas of CheeseMagnum                Flying in a Blue DreamMagnum                Black Swans and Wormhole WizardsMagnum                Somewhere in TimeMagnum                Big Red CarMr Percival           KillersMr Percival           PowerslaveMr Percival           Surfing with the AlienMr Percival           Heavy as a Really Heavy ThingMr Percival           Yummy YummyMr Percival           Out of the LoopMr Percival           Suck on ThisMr Percival           Pork SodaMr Percival           Sailing the Seas of CheeseMr Percival           Flying in a Blue DreamMr Percival           Black Swans and Wormhole WizardsMr Percival           Somewhere in TimeMr Percival           Big Red CarNoiseworks            KillersNoiseworks            PowerslaveNoiseworks            Surfing with the AlienNoiseworks            Heavy as a Really Heavy ThingNoiseworks            Yummy YummyNoiseworks            Out of the LoopNoiseworks            Suck on ThisNoiseworks            Pork SodaNoiseworks            Sailing the Seas of CheeseNoiseworks            Flying in a Blue DreamNoiseworks            Black Swans and Wormhole WizardsNoiseworks            Somewhere in TimeNoiseworks            Big Red CarPat Metheny           KillersPat Metheny           PowerslavePat Metheny           Surfing with the AlienPat Metheny           Heavy as a Really Heavy ThingPat Metheny           Yummy YummyPat Metheny           Out of the LoopPat Metheny           Suck on ThisPat Metheny           Pork SodaPat Metheny           Sailing the Seas of CheesePat Metheny           Flying in a Blue DreamPat Metheny           Black Swans and Wormhole WizardsPat Metheny           Somewhere in TimePat Metheny           Big Red CarPrimus                KillersPrimus                PowerslavePrimus                Surfing with the AlienPrimus                Heavy as a Really Heavy ThingPrimus                Yummy YummyPrimus                Out of the LoopPrimus                Suck on ThisPrimus                Pork SodaPrimus                Sailing the Seas of CheesePrimus                Flying in a Blue DreamPrimus                Black Swans and Wormhole WizardsPrimus                Somewhere in TimePrimus                Big Red CarSlayer                KillersSlayer                PowerslaveSlayer                Surfing with the AlienSlayer                Heavy as a Really Heavy ThingSlayer                Yummy YummySlayer                Out of the LoopSlayer                Suck on ThisSlayer                Pork SodaSlayer                Sailing the Seas of CheeseSlayer                Flying in a Blue DreamSlayer                Black Swans and Wormhole WizardsSlayer                Somewhere in TimeSlayer                Big Red CarSteve Vai             KillersSteve Vai             PowerslaveSteve Vai             Surfing with the AlienSteve Vai             Heavy as a Really Heavy ThingSteve Vai             Yummy YummySteve Vai             Out of the LoopSteve Vai             Suck on ThisSteve Vai             Pork SodaSteve Vai             Sailing the Seas of CheeseSteve Vai             Flying in a Blue DreamSteve Vai             Black Swans and Wormhole WizardsSteve Vai             Somewhere in TimeSteve Vai             Big Red CarStrapping Young Lad   KillersStrapping Young Lad   PowerslaveStrapping Young Lad   Surfing with the AlienStrapping Young Lad   Heavy as a Really Heavy ThingStrapping Young Lad   Yummy YummyStrapping Young Lad   Out of the LoopStrapping Young Lad   Suck on ThisStrapping Young Lad   Pork SodaStrapping Young Lad   Sailing the Seas of CheeseStrapping Young Lad   Flying in a Blue DreamStrapping Young Lad   Black Swans and Wormhole WizardsStrapping Young Lad   Somewhere in TimeStrapping Young Lad   Big Red CarThe Tea Party         KillersThe Tea Party         PowerslaveThe Tea Party         Surfing with the AlienThe Tea Party         Heavy as a Really Heavy ThingThe Tea Party         Yummy YummyThe Tea Party         Out of the LoopThe Tea Party         Suck on ThisThe Tea Party         Pork SodaThe Tea Party         Sailing the Seas of CheeseThe Tea Party         Flying in a Blue DreamThe Tea Party         Black Swans and Wormhole WizardsThe Tea Party         Somewhere in TimeThe Tea Party         Big Red CarThe Wiggles           KillersThe Wiggles           PowerslaveThe Wiggles           Surfing with the AlienThe Wiggles           Heavy as a Really Heavy ThingThe Wiggles           Yummy YummyThe Wiggles           Out of the LoopThe Wiggles           Suck on ThisThe Wiggles           Pork SodaThe Wiggles           Sailing the Seas of CheeseThe Wiggles           Flying in a Blue DreamThe Wiggles           Black Swans and Wormhole WizardsThe Wiggles           Somewhere in TimeThe Wiggles           Big Red CarWayne Jury            KillersWayne Jury            PowerslaveWayne Jury            Surfing with the AlienWayne Jury            Heavy as a Really Heavy ThingWayne Jury            Yummy YummyWayne Jury            Out of the LoopWayne Jury            Suck on ThisWayne Jury            Pork SodaWayne Jury            Sailing the Seas of CheeseWayne Jury            Flying in a Blue DreamWayne Jury            Black Swans and Wormhole WizardsWayne Jury            Somewhere in TimeWayne 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.