1. Joining tables
In this chapter, we'll look some of the most common ways of joining tables to create more expansive queries.
2. Relational Databases
SQL Server is a relational database management system. One of the key principles of a relational database is that data is stored across multiple tables.
We'll need to be able to join tables together in order to extract the data we need.
3. Primary Keys
We use PRIMARY and FOREIGN keys to join tables.
A primary key is a column that is used to uniquely identify each row in a table.
This uniqueness can be achieved by using a sequential integer as an identity column. Or, sometimes, existing columns naturally contain unique values and they can be used.
Here we can see the first few rows from the artist table.
It has 2 columns, artist_id and name.
The artist_id column acts as the primary key for this table. It is an integer column, and each value is different.
4. Foreign Keys
Now let's look at the album table. Can you spot the primary key?
Yes, it's the album_id column. It's common for the primary key to be named "table-name underscore id".
But, you'll have noticed there is also an artist_id column. That also consists of integers, and has the same name as the artist_id column in the artist table.
What does this mean?
5. Foreign keys
Well, the artist_id in the album table, acts as a FOREIGN KEY to the artist table.
What this means, is that when the artist_id in the album table MATCHES the artist_id in the artist table, those rows can be linked using a JOIN.
Let's see how that looks.
6. Joining album and artist
Rows 1 and 4 of the album table have an artist_id of 1.
The artist_id 1 in the artist table is AC/DC.
7. Joining album and artist
So, when we join the two tables together, we return the album details from the album table, and the corresponding artist details from the artist table - joined using the artist_id field, which is common to both tables.
8. INNER JOIN
We just saw the results of an INNER JOIN.
We join the album and artist tables by matching the artist_id from the artist table to the artist_id in the album table.
When selecting columns with the same name from different tables, you must fully qualify the column name. Otherwise, SQL Server will not know which table to SELECT the column from.
To fully qualify a column, you have to prefix it with the relevant table name, followed by a period.
9. INNER JOIN syntax
Here's a generic example to remind you of the syntax.
We select the relevant columns from the main table, - table_A in this case, then SELECT any additional columns from the table we want to join to, which in this case is table_B.
Then we specify the join using the keywords INNER JOIN and ON, providing the necessary key columns from both tables.
10. INNER JOIN - No WHERE clause
In this example, we don't specify a WHERE clause, so we return ALL combinations of ALL matches between the artist and album tables, again, based on the artist_id column which is common to both.
11. Multiple INNER JOINS
You can join more than 2 tables using INNER JOINS.You simply provide an additional INNER JOIN for each combination of tables. In our code above, we join tables A and B, and also B and C. We'll work through an example of joining 3 tables with INNER JOINs later in this chapter.
12. Let's join some tables!
Let's practice joining some tables!