Joins in an SQL database might seem like a confusing topic,
but by the end of this video you'll have a much better understanding.
We'll cover what a join is, see examples of different join types,
and I'll share a range of tips on using joins.
Let's get into it.
So, what is a join?
A join is a feature of SQL that allows you to get data from two different tables where
the records are related. They are used in all queries that involve multiple tables,
which is a lot of queries that you'll write or see as a developer.
Why do you need to use joins? It's because a well-designed database has its data split
into several tables. There are several reasons for this, which I've covered in another video.
But you'll use joins to get data from related tables into a single query
and a single set of results.
Let's take a look at some examples of joins.
Here are two sample tables we'll use for our examples of joins.
One table contains person information: we've got a person ID which is their unique identifier.
We have a first name, and we have a company ID.
The other table is the company table.
This contains an ID, which is a unique identifier, and the company name.
The company ID in the person table represents the company ID in the company table.
This means that John, who has a company ID of 2, is related to the company XYZ Services,
because that company has a company ID of 2.
This data is in two tables. We can select from these tables individually,
but we would have two different queries and two different results.
What if we want the data in one result? What if we wanted to see
a list of people's names and the company names they are related to?
We can use a join.
We start by writing the select query, then some columns from the person table.
We specify the person table,
then we need to specify the second table. Before the second table, we specify a join.
There are a few different types of joins we can use in SQL. The differences are
in how they handle data found in one table but not another.
The first type of join we'll look at is called an Inner Join.
An Inner Join lets you relate records between two tables,
and returns records where there is a match in both tables.
If there is a record in one table without a match in the other, it's not shown in the results. For
example, if there is a person that has no company ID record, the person won't be shown if we use an
Inner Join. Or, if there is a company with no person records, the company won't be shown.
We'll only see person records and company records that match.
How do we use this type of join?
An Inner Join is written by specifying the word JOIN or the words INNER JOIN between
the first table and the second table. In our query, we specify person INNER JOIN company.
The INNER word is optional, but you'll notice I've written it here.
I prefer to include the word INNER just so it's clear what type of join it is.
But, if you prefer to leave it out, you can just write JOIN, and you'll get the same results.
After the inner join, we have our second table. We write the second table of company.
We then have to specify how the two records are related.
What fields in our tables show us that a person is related to a company?
Looking at our table here, we can see that the company ID
is in the person table and the company table. This is the field that relates the two tables.
You can tell this in other databases by the fact that there is a foreign key,
which is a way to define how one table is related to another. The field names may also be the same,
but they don't have to be, and are actually often different.
So, we can specify the company ID in our query.
Back in our query, we add the word ON, then we specify the conditions where a row matches.
We specify person.company_id = company.id. This means that the company ID in the person
table needs to match the id in the company table. If it does, the records are related.
Now we've joined the tables, we can update what data we want to see. We go back to our Select
clause and add in more columns. We'll add in the company name here, as we want to see that.
Our query is finished. We can now run the query!
There may be one more thing you want to do before running it. We may want to specify the table name
for each column in the Select clause. This way it's clear which table the columns come from.
This is helpful if there are fields that have the same name in different tables.
So, update the query to add the table names. Now we can run it.
This should give us the following results. We can see all person records and their matching company
records if they have a match. We can see data from both the person table and the company table.
There are some records that did not show. Person 1, Susan, did not show because they
had no company. Company 3 and 5 did not show because they did not have any person records.
This is because we used an Inner Join.
So that's the power of using a join.
It lets you combine data from two tables into one query and one result.
We've just covered the inner join type. What other join types are there?
Another join type is a left outer join. This join type will show all records from the table
on the left of the join keyword, which is the table specified first. Then, if there are any
records matching in the right table, which is the one on the right of the join keyword,
then they are also included. If there are no matches, null values are shown.
So, for our query, this would show all person records,
regardless of if they have a related company or not.
Let's try it in our query.
Here's our query. Instead of Inner Join, we can use the keyword Left Outer Join,
or Left Join. Both of them do the same thing.
We're specifying that the left table, which is person, will show all records,
and the company table will show either null values for its columns or the matching values.
Let's run the query.
If we run the query we'll get these results. We can see they are different from before because
we now see all person records. For the person record here, Susan, that has no company record,
we still show the person, but a null value will be shown for the company name.
This type of join is helpful if you want all records in a table
even if there are no matches in another table.
Another type of join is a Right Outer Join.
This is the same as a Left Outer Join, except the tables are referred to in the opposite way.
All records from the right table are shown, and if there is a matching record in the left table
it is shown, if not then a null value is shown.
A query using a Right Outer Join, or Right Join, can be written like this.
This will show all company records, and related person records,
but will show NULL for the person fields if no match is found.
Here are the results of that query. We can see that company 3 and 5 are showing
even though there are no related person records.
A left outer join is the same as a right outer join, except the tables are different.
This means you can swap the tables in a right outer join,
change it to a left outer join, and get the same results.
I hardly ever use a Right Outer Join, because it can be written as a Left Outer Join instead,
and it makes it easier for me to understand. But you may find times where you need to use it.
Finally, a Full Outer Join is a type of join that
shows a combination of both a left and a right outer join.
Our query looks like this. We can use either Full Join or Full Outer Join.
The order of the tables does not matter.
Here are the results of the query. We can see that matching rows are shown. We can also see rows in
the person table without a matching company, and company rows without a matching person.
You may not use this type of join very often, but it's good to know it exists.
Now we've covered the main types of joins,
let's get into some tips or common questions about working with joins.
The first thing you may be wondering is, "Are joins bad?"
I heard this in a team I worked in once, who said they wanted to reduce joins
as joins will reduce the performance of a query.
Joins aren't a bad feature.
Having joins in your query is not a bad thing and won't necessarily slow it down.
It's a sign that your database is well designed and your data
is in different tables where it should be.
If you start getting performance problems in your
query, then there are a range of things you can do to improve it.
So, don't avoid joins just because you may think it would slow down your query.
The next tip is about other join keywords. There are some other keywords that let you
join tables together. One of those is the USING keyword. This lets you specify one field name,
and the tables are joined on that field.
However, while it may simplify your query,
I would advise against using it. This is because your query will break if you change field names.
Or if you have the same field name but they mean different things, it won't work.
I recommend using the ON keyword and specifying the fields using their names.
The other keyword is a NATURAL JOIN. This allows you to join two tables without specifying what
fields they join on. The database will perform a join on all of the columns that have the same
name. For example, if both tables have a company ID field, the join will be done on that field.
However, I also advise against using this join type. It restricts you to using field names that
match, and if you make adjustments to column names there is a risk your query will break.
So, stick to using the Inner Join and specifying the names of the fields.
The third tip is about an alternative way of using joins.
You may have seen another way to write joins. Or, you may be thinking,
joins are just a way to get data from two tables where one field matches another field,
and this sounds like a Where clause.
So, why not write a join using a where clause?
Here's an example of a query that uses a where clause instead of joins.
You can select from both the person and the company table.
You specify the table names in the FROM clause, separated by commas.
You have a WHERE clause that says where person company ID equals company ID.
This makes sense, right? You can run this query,
and you'll get the same results as your INNER JOIN query from before.
However, there are a couple of issues with this, and I don't recommend writing joins in this way.
First, SQL has an ANSI standard, and the standard way of writing joins is using the keywords.
A minor point, but good to know.
Second, this limits you to an inner join.
You can specify outer joins in Oracle using extra syntax, but that's only for Oracle.
Third, it gets confusing if you have more where clauses. You then have conditions that perform
joins on tables and conditions that filter out data. It's mixing two concepts into one.
Finally, separating the tables in the From clause from the joins in the Where clause can be risky.
SQL lets you add multiple tables and join to them,
so once you start seeing queries with 4, 5, or even 10 tables, it's important to get the
joins right. If you join to 10 tables and have your WHERE clause specify the matching columns,
it's easy to miss out one join criteria and your data will be incorrect. With the JOIN keywords,
you need to have a keyword for each table, so there's no risk of missing a join criteria.
If you want to learn how to join three or more tables together,
check out my other video in the description which takes you through the steps to do that.
And that brings us to the end of this video on joins in SQL.
If you learned something new from this video, make sure to subscribe to my channel.
If you want to learn more about database design and development,
visit databasestar.com. That’s where I share my best database-related content.
Which step from this tutorial was the most helpful? Was it the differences between the
different join types, or seeing the join type examples in action, or something else?
Thanks for watching.