In this blog post, you will learn everything you need to know about SQL joins. This includes real examples of the SQL inner join, left join, right join, and full outer join as well.

If this is our first time meeting, I’ve been a SQL DBA and SQL Developer for over 8 years at large enterprises. I’m also the highest SQL Server freelancer on Upwork.com, where I’ve earned over $670k and completed over 120 SQL jobs as well.

Upwork Profile: Joshua Burns SQL Server DBA | SQL DEV

Disclaimer

The post may contain referral links and I earn a small commission if you choose to use them at no additional cost to you. In some cases, I have locked in exclusive deals for you.

Environment Setup

First, we need to setup an environment that you can follow along with and get real hands on experience.

We are going to use the SQL Server 2019 Developer edition, which is completely free for you to download. You can use the webpage below to download and install the developer edition for free.

Download Here: SQL Server 2019 Developer Edition

However, you can use any recent version of SQL Server to follow along with this video. If you need help installing SQL Server, checkout the video below. Just be sure to download the SQL Server 2019 developer edition, rather than the free trial version.

Step-By-Step Guide: Install and Configure SQL Server 2019

I definitely recommend that you watch the associated YouTube video while going through this blog post.

Watch Here: SQL Server Joins for Beginners with Examples

After you install and configure SQL Server 2019 developer edition, next you need to execute the SQL script below to create the test database and tables. This goes without saying, but all data below is fake data.

-- Create sample database --
CREATE DATABASE [JoshBurnsTech]
GO

-- Create tables --
USE [JoshBurnsTech]
GO

CREATE TABLE [dbo].[Client]
(
	[ClientID] int IDENTITY(1,1) NOT NULL,
	[FirstName] nvarchar(50) NULL,
	[LastName] nvarchar(50) NULL,
	[EmailAddress] nvarchar(50) NULL,
	[Phone] nvarchar(25) NULL,
    CONSTRAINT [PK_Client_ClientID] PRIMARY KEY CLUSTERED ([ClientID] ASC)
)

CREATE TABLE [dbo].[Address]
(
	[AddressID] int IDENTITY(1,1) NOT NULL,
	[ClientID] int NULL,
	[AddressLine] nvarchar(60) NULL,
	[City] nvarchar(30) NULL,
	[State] nvarchar(20) NULL,
	[CountryRegion] nvarchar(50) NULL,
	[PostalCode] nvarchar(15) NULL,
    CONSTRAINT [PK_Address_AddressID] PRIMARY KEY CLUSTERED ([AddressID] ASC)
)

ALTER TABLE [dbo].[Address]
ADD CONSTRAINT FK_Client_ClientID FOREIGN KEY (ClientID)
	REFERENCES [dbo].[Client] (ClientID)

-- Insert sample data --
INSERT [dbo].[Client] VALUES
(
	'Elon',
	'Musk',
	'elon.musk@tesla.com',
	'111-111-111'
)

INSERT [dbo].[Client] VALUES
(
	'Jeff',
	'Bezos',
	'jeff.bezos@amazon.com',
	'222-222-222'
)

INSERT [dbo].[Client] VALUES
(
	'Charles',
	'Hoskinson',
	'charles.hoskinson@cardano.com',
	'333-333-333'
)

INSERT [dbo].[Client] VALUES
(
	'Cathie',
	'Wood',
	'cathie.wood@arkinvest.com',
	'444-444-444'
)

INSERT [dbo].[Address] VALUES
(
	'1',
	'2681 Eagle Peak',
	'Bellevue',
	'Washington',
	'United States',
	'98004'
)

INSERT [dbo].[Address] VALUES
(
	'2',
	'0987 Cedar Creek Stores',
	'Branch',
	'Minnesota',
	'United States',
	'55056'
)

INSERT [dbo].[Address] VALUES
(
	'3',
	'2345 Blue Ridge Mall',
	'Johnson Creek',
	'Wisconsin',
	'United States',
	'53038'
)

INSERT [dbo].[Address] VALUES
(
	 NULL,
	'5678 Horizon Parkway Center',
	'Kansas City',
	'Missouri',
	'United States',
	'64106'
)

SQL Inner Join

The reason why you join SQL tables together is to combine rows from two or more tables based on a related column between them. Most often this will be a primary key to foreign key relationship. 

Inner joins allow you to select records that have matching values in both tables. In the illustration below, stars represent the records that will be returned from the inner join. The inner join returns all rows from both tables as long as there is a match between the columns.

SQL Inner Join Example

Let’s write an inner join in SQL Server! Start at 5:22 of the YouTube video, so that you can follow along with the example and listen to my in depth explanation as well.

As depicted in the database diagram below, we are going to join the tables on ClientID, which the PK to FK relationship. 

SQL Left Join

The SQL left join returns all records from the left table and the matching records from the right table.

In the illustration below, all records are returned from the client table (left table) with matching records from the address table (right table).

SQL Left Join Example

Let’s write a left join in SQL Server! Start at 11:03 in the YouTube video, so that you can follow along with the example and listen to my in-depth explanation as well.

SQL Right Join

The SQL right join returns all records from the right table and the matching records from the left table. Right join is the opposite of the left join. 

In the illustration below, all records are returned from the address table (right table) with matching records from the client table (left table).

SQL Right Join Example

Let’s write a left join in SQL Server! Start at 15:14 in the YouTube video, so that you can follow along with the example and listen to my in-depth explanation as well.

SQL Full Outer Join

The SQL full outer join returns everything, both matched and unmatched records from the left and right table.

In the illustration below, all records are returned from both the client table (left table) and right table (address table).

SQL Full Outer Join Example

Let’s write a full outer join in SQL Server! Start at 18:48 in the YouTube video, so that you can follow along with the example and listen to my in-depth explanation as well.

Conclusion

To summarize everything, below are simple explanations of SQL joins.

• Inner Join: returns matches between both tables in the join
• Left Join: returns all records from the left with matching records from the right table
• Right Join: returns all records from right table with matching records from the left table
• Full Outer Join: returns all records (both matched and unmatched) from the left and right table

Need more help with SQL and freelancing? Join my Patreon today which includes benefits like:

• Upwork mentoring
• Private discord server
• Exclusive content
• Access to my cryptocurrency portfolio

Join Today: Josh Burns Tech Patreon

What’s a SQL topic that you want to learn more about? I want to help, so let me know in the comments below.

Also, take a second to follow me on Instagram and Twitter for more freelance help, insight into my daily work ethic and grind, and see all other things that I enjoy most in life.