SQL JOIN Commands: Working with Multiple Tables

Relational databases organize data into tables. So far, we have only queried a table at a time.

In this article, you'll learn how to query multiple tables. These tables are related and have common column(s).

JOIN Command

JOIN commands combine records/rows from multiple tables.

Here are some types of JOIN supported by SQLite.

1. Inner Join: Returns records with matching column values

2. Left Join(Outer Join): Returns all records from the first table and only matching records from the second table

3. Cross Join: Returns all the possible combinations of records from both tables(We'll use two tables for simplicity)

There are other joins not directly supported by SQLite:

• Right Join(Right Join): Returns all records from the second table and only matching records from the first table

• Full Outer Join: Returns all records from both tables

INNER JOIN Command

It returns rows from both tables with matching columns.

SELECT * selects all columns from the combined table
items.SizeId=sizes.SizeId specifies the join condition. items.SizeId is the 'SizeId' column in the items table while sizes.SizeId is the 'SizeId' column in the sizes table.

These column values are to be compared to perform the join operation.

Here is a way you can imagine the INNER JOIN command:

1. The 'items' table is the reference.
2. The JOIN command takes the rows in the 'items' table one after the other
3. It checks the value in the column specified in the JOIN condition('SizeId' in this case) and tries to find a matching value in the 'SizeId' column of the 'sizes' table
4. When it finds a match, it combines both rows and moves to the next one. If it doesn't find a match, it drops the row. The JOIN command continues this operation till it gets to the last row.

Let's perform a simple JOIN command on our sample database

Open your database and click on 'Execute SQL'

Let's join the 'albums' and 'artists' tables.

Our JOIN condition will be on the 'ArtistId'. The 'ArtistId' column is a primary key column in the 'artists' table and a foreign key column in the 'albums' table.

We have to use primary and foreign key columns in our join condition to combine every artist with the respective album.

Run the code below in your editor

SELECT * FROM 
albums JOIN artists
on albums.ArtistId = artists.ArtistId;

SELECT * selects all the columns in the combined table

As you can see, every album has been matched to an artist. Also check the values in the 'ArtistId' columns, they are the same.

The JOIN command drops rows without matches. If we have a value in the 'ArtistId' column of the 'albums' table that doesn't exist in the 'artists' table, that row would be dropped.

Imagine we updated our 'albums' table with a new album and artist but didn't include that new artist in our 'artists' table. That row will be dropped.

Note that we did 'albums JOIN artists' but not 'artists JOIN albums'. This is because the 'albums' table has the foreign key column(ArtistId) that relates to the 'artists' table but not vice versa.

We can also see that the combined table has multiple 'ArtistId' columns. This is how the JOIN commands work but don't you worry, we can specify columns here.

Let's select the 'AlbumId','Title', and 'Name' columns

SELECT AlbumId, Title, Name FROM
albums JOIN artists
on albums.ArtistId = artists.ArtistId;

LEFT JOIN Command

This includes all the rows from table 1 and only matching rows from table 2.

SELECT * selects all columns from the combined table items.SizeId=sizes.SizeId specifies the join condition. 'items.SizeId' is the 'SizeId' column in the items table while 'sizes.SizeId' is the 'SizeId' column in the 'sizes' table.

Here is a way you can imagine the LEFT JOIN command:

The left join Command returns all the rows in first table then starts to check the values of the specified column and checks for matches in the second table.

When it finds a match, it combines it. If it doesn't, it fills in null values

It repeats this process till it gets to the last row of the first table

Please note that the first specified row in the left join command is the table that is used as reference.

a LEFT JOIN b returns all records of a and matching records of b

b LEFT JOIN c returns all records of b and matching records of c

Let's run a query to see if some artists don't have albums.

SELECT Name, Title FROM
artists LEFT JOIN albums
on artists.ArtistId = albums.ArtistId;

We specified the 'Name' and 'Title' columns just like we did in the JOIN part earlier.

Scroll down to see Null values filled for some records in the 'Title' column.

We have some artists appearing twice in the combined table because JOIN commands combine rows with their matches. So, if a row has 5 matches, it will return 5 rows consisting of every matching row.

To view only artists that don't have albums, we can use the WHERE condition.

SELECT Name, Title FROM
artists LEFT JOIN albums
on artists.ArtistId = albums.ArtistId
WHERE Title IS NULL;

What if we want to know the number of artists with no albums.

SELECT COUNT(*) FROM
artists LEFT JOIN albums
on artists.ArtistId = albums.ArtistId
WHERE Title IS NULL;

CROSS JOIN Command

This combines all the records from table1 with all records from table2.

If we have 3 records from table1 and 2 records from table2. Cross Join would return 6(3x2) records.

It doesn't need a join condition like the other join commands

UNION Operator

This helps us to stack tables on each other. The tables must have the equal number of columns and these columns must be of the same data types(column1 in table1 must have the same data type as column1 in table2).

WITH Command

This command helps us to create a temporary table. We can perform operations on result sets with the WITH command.

WITH table1 AS (SELECT ..........)
SELECT x, y, z
FROM table1;

Our temporary table is inside the parentheses. It is also known as a subquery.

Let's use the WITH command to find the number of albums by every artist. Our temporary table would be the combined table from the INNER JOIN part above.

Run the query as shown below

WITH temp_table AS (SELECT AlbumId, Title, Name FROM
albums JOIN artists
on albums.ArtistId = artists.ArtistId)
SELECT Name, COUNT(*) 
FROM temp_table
GROUP BY Name;

Please note that the semi-colon is missing after creating our temporary table in the parentheses. The semi-colon should only be included at the end of the whole query.

Summary

• Inner Join returns records with matching column values
• Left Join returns all records from the first table and only matching records from the second table
• Cross Join returns all the possible combinations of records from both tables
• The Union operator stacks tables on each other
• The WITH Command allows us to write sub-queries which help us to work with multiple tables.