Introduction To SQL Joins
Databases are commonly referred to as relational because they contain tables of data, some of which relates to data in other tables.
For a SQL Server query to extract useful data from these related tables, you will need to understand how the tables connect to each other.
A SQL Join clause creates a new table containing the columns from one or more tables where the condition in the join operation is met.
The relationship between tables is expressed (or described) as a type of SQL join.
This article gives you a clear, step-by-step introduction to the four different SQL join types.
To learn more about SQL joins, sign up for one of our SQL courses.
SQL Join Types
There are essentially four useful join types in SQL.
The type of SQL join or join condition you use will depend on exactly which matching rows you want to extract.
- Inner Joins: Find records where the values match in both tables
- Left Outer Joins: Return all records from the left table and only records from the right table that match.
- Right Outer Joins: Return all records from the right table and only records from the right table that match.
- Full Outer Joins: Return all records from the left and right tables.
SQL Joins can find matching rows in two or more tables.
We have only used two tables in this article to keep things simple. However, you can use SQL joins on multiple tables.
Note:
You will see different join syntax used sometimes.
There is no difference between a right join and a right outer join. The same applies to left join and left outer join.
We cover more advanced SQL Joins in other articles:
- SQL Joins With Three Or More Tables
- SQL Cross Join
Expressing A SQL Join Type
The SQL join type between two tables is expressed via the relationship that each table has with another SQL table via its primary (PK) or foreign (FK) keys.
For example, we may have two related tables whose relationship is expressed as follows:
“One publisher can publish Many books”
Note that here we are also saying that “One book cannot have Many publishers”
Remember that the primary key is always the One side of a relationship. The FK is always the Many side of the relationship.
See this article to learn more about primary keys and foreign keys.
Our Example Data
To demonstrate the different join types in SQL, we will use some simple data.
tbl_publisher
This table has a total of 18 publishers.
Four publishers have not published any books – 15, 16, 17, 18 – we see more about this below.
tbl_books
This table has a total of 25 books.
There is an error in our data as there is one book with a publisher_id of 0 that does not exist.
There are no books that match for four of our publisher_id’s 15, 16, 17, 18
The relationship between the publisher and books table is visualised below.
As you can see, “One publisher can publish Many books”.
This shows that the relationship we need to use is between the (PK) tbl_publisher.publisher_id and (FK) tbl_books.publisher_id
We could express this as follows:
select * from tbl_books, tbl_publisher
where tbl_books.publisher_id = tbl_publisher.publisher_id
However, this is better expressed using SQL join type expressions.
Note: If you are using a live database rather than example data, please see this article to make sure your database is fully backed up.
Extracting Our Data using Join Type Expressions
This section will use the four SQL join expressions to see how we can use them to extract data from these two tables.
Which Table Is Left And Which Is Right In A SQL Join?
Left and Right refer to where a table resides in the FROM clause of the join clause that you will be using.
The left table is the table in the FROM clause and the one on the left of the SQL join clause you are using.
The right table is the one on the right of the SQL join clause.
If this isn’t clear, don’t worry.
It will become apparent in the SQL Join scripts that we show you below.
Note: Joins are separate from union queries with ‘combine’ rather than join data.
Inner Join
With a SQL inner join, we are saying: “extract all books that have matching publishers”.
Visually, we would express this as a Venn diagram:
select * from tbl_books INNER JOIN tbl_publisher
ON tbl_books.publisher_id = tbl_publisher.publisher_id
Notice that this INNER JOIN example extracts the 25 books with matching publishers.
Left Outer Join
With a left outer join, we are saying: “extract all books that have and those that have not got matching publishers”.
Visually, we would express this as a Venn diagram:
The SQL to carry out this is
select * from tbl_books LEFT OUTER JOIN tbl_publisher
ON tbl_books.publisher_id = tbl_publisher.publisher_id
Notice that this LEFT OUTER JOIN example extracts the 26 books that have matching publishers and also includes the one book that had a non-matching tbl_publisher.publisher_id
Right Outer Join
With a right outer join, we are saying: “extract all publishers that have and have not got matching books”.
Visually, we would express this as a Venn diagram:
select * from tbl_books RIGHT OUTER JOIN tbl_publisher
ON tbl_books.publisher_id = tbl_publisher.publisher_id
Notice that this RIGHT OUTER JOIN example extracts the 29 publishers that have matching books and also includes the four publishers that had a non-matching tbl_books.publisher_id
Full Outer Join
With a full outer join, we are saying: “extract all publishers and books regardless of whether they have matching publisher_id’s”.
Visually, we would express this as a Venn diagram:
select * from tbl_books FULL OUTER JOIN tbl_publisher
ON tbl_books.publisher_id = tbl_publisher.publisher_id
Notice that this FULL OUTER JOIN example extracts the 30 books with matching publishers.
It also includes the one book that did not have a matching tbl_publisher.publisher_id and also consists of the four publishers that had a non-matching tbl_books.publisher_id
Simplifying SQL Joins
A simple way to make your SQL joins easier to read using column aliases. Using aliases also helps avoid problems for SQL interpreting your query.
We cover table aliases fully in this article on table aliases.
Fully qualifying fields
One issue you may come across when querying fields from more than one table is that you may need to qualify field names fully to stop an ambiguity error.
For example, this query will generate an error:
select book_id, title, author, publisher_id, publisher from tbl_books, tbl_publisher where tbl_books.publisher_id = tbl_publisher.publisher_id
Msg 209, Level 16, State 1, Line 1
Ambiguous column name ‘publisher_id’.
SQL does not understand which publisher_id you require. Is it the one from the tbl_books table or the tbl_publisher table?
This is where you are required to fully qualify the field name as below:
select book_id, title, author, tbl_books.publisher_id, publisher from tbl_books, tbl_publisher where tbl_books.publisher_id = tbl_publisher.publisher_id
Bearing this in mind, we often see all fields fully qualified as below:
SELECT tbl_books.book_id, dbo.tbl_books.title, tbl_books.author, tbl_books.publisher_id, tbl_publisher.publisher
FROM tbl_books INNER JOIN tbl_publisher
ON tbl_books.publisher_id = tbl_publisher.publisher_id
For this reason, many coders use shorthand expressions to simplify the SQL.
Using Shorthand Table Alias Expression
To simplify the above SQL, we can alias the table names with a simple, unique letter/nameto simplify the above SQL.
Then we replace the fully qualified name with the alias.
Here, tbl_books has been aliased as B, and tbl_publisher has been aliased as P.
SELECT B.book_id, B.title, B.author, P.publisher_id, P.publisher
FROM dbo.tbl_books B INNER JOIN
dbo.tbl_publisher P ON B.publisher_id = P.publisher_id
Note how much more straightforward the whole SQL code is.
Conclusion
SQL Joins are fundamental to working with relational databases.
You will need to master them early on as you develop your skills as a data analyst or database administrator so go and give them a try today!
If you want to keep learning SQL but don’t have much free time, you can try the SQL learning apps available through your iPhone or Android phone’s app stores. You’ll be able to learn the theory and put it into practice by completing exercises.
