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.

Introduction To SQL Joins 1

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

Introduction To SQL Joins 2

The relationship between the publisher and books table is visualised below. 

As you can see, “One publisher can publish Many books”.

Introduction To SQL Joins 3

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:

Introduction To SQL Joins 4

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:

Introduction To SQL Joins 5

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:

Introduction To SQL Joins 6

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:

Introduction To SQL Joins 7

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.

Related Articles

SQL IF Statements

SQL Update Statement

About Ben Richardson

Ben is a director of Acuity Training. He writes about SQL, Power BI and Excel on a number of industry sites including SQLCentral, SQLshack and codingsight.