SQL JOIN Types with Examples

By: Jim Evans   |   Updated: 2021-12-15   |   Comments (1)   |   Related: > JOIN Tables

Problem

I am learning about SQL JOINs and could use some examples to get started with the Microsoft SQL Server relational database engine.  Can you please outline the various SQL JOIN types, provide examples and additional information as a point of reference?

Solution

Below is a list summarizing different types of SQL joins with generic examples to each. Each JOIN type includes a link to a MSSQLTips tutorial that explains the JOIN in detail and provides examples of each. This tutorial should be used as a quick reference guide.

Create Sample Tables and Data Sets

Here is a script to create sample tables and data.

CREATE TABLE dbo.TableA (col1 int, col2 char(5))
CREATE TABLE dbo.TableB (col1 int, col2 char(5))
CREATE TABLE dbo.TableC (col1 int, col2 char(5))
CREATE TABLE dbo.TableD (col1 int, col2 int, col3 char(5))
CREATE TABLE dbo.TableE (col1 int, col2 int, col3 char(5))

INSERT INTO dbo.TableA VALUES (1,'A'), (2,'A'), (3,'A')
INSERT INTO dbo.TableB VALUES (1,'B'), (2,'B'), (4,'B')
INSERT INTO dbo.TableC VALUES (1,'C'), (2,'C'), (4,'C')
INSERT INTO dbo.TableD VALUES (1,1,'A'), (2,2,'A'), (3,3,'A')
INSERT INTO dbo.TableE VALUES (1,1,'B'), (2,2,'B'), (4,4,'B')

Check out these additional resources:

• Create Tables in SQL Server with T-SQL
• SQL Server Primary Key
• How to create a SQL Server foreign key

SQL Inner Join

The SQL Inner Join is a common join to combine results from 2 or more tables in a SELECT statement. This will return the matching rows where the matched column values exist in both tables.  Here is the syntax:

--1) INNER JOIN
SELECT *
FROM dbo.TableA as a 
   INNER JOIN dbo.TableB as b 
   	ON a.Col1 = b.Col1;
GO

Here is the result set:

To learn more about common SQL queries check out the following:

• Getting Started with SQL INNER JOIN
• SQL Server Join Example
• Join SQL Server tables where columns include NULL values
• Understanding SQL Server Physical Joins

Right Outer Join

The SQL RIGHT Outer Join returns all rows from the Right table and rows that match from the Left table, showing NULL values for the Left table columns that do not match.

With this Join type in the example below the results will include all rows from Table B and will show values from TableA where the join criteria match, else it will show NULL values for Table A columns. Here is the syntax:

--2) RIGHT OUTER JOIN
SELECT *
FROM dbo.TableA as a 
   RIGHT OUTER JOIN dbo.TableB as b 
	ON a.Col1 = b.Col1;
GO

Here is the result set:

To learn more, check out SQL RIGHT JOIN Examples

Left Outer Join

The SQL LEFT Outer Join is used to get all rows from the Left Table (TableA) and the rows that match from the Right table (TableB), showing NULLs for the columns for the Right table (TableB) that do not match. Another way to state this may be: give me all rows from TableA and show the rows from TableB that match and show NULLs for the TableB columns where the rows do not meet the Join criteria to TableA. Note: This is the opposite of the Right Join above. Here is the syntax:

--3) LEFT OUTER JOIN
SELECT *
FROM dbo.TableA as a 
   LEFT OUTER JOIN dbo.TableB as b 
   	ON a.Col1 = b.Col1;
GO

Here is the result set:

To learn more, check out SQL LEFT JOIN Examples

Full Outer Join

The SQL FULL Outer join returns all rows from each table and returns NULLs for the Columns that do not match. This is like a combined Right and Left Join. Here is the syntax:

--4) FULL OUTER JOIN 
SELECT *
FROM dbo.TableA as a 
   FULL OUTER JOIN dbo.TableB as b 
   	ON a.Col1 = b.Col1;
GO

Here is the result set:

To learn more, check out SQL FULL OUTER JOIN with Examples

Cross Join

The SQL Cross join returns every combination of rows between the joined tables. See the article linked below to learn more about this join. Here is the syntax:

--5) CROSS JOIN
SELECT *
FROM dbo.TableA as a 
   CROSS JOIN dbo.TableB as b;
GO

Here is the result set:

To learn more, check out Learning SQL CROSS JOIN with Examples and Calculate Running Totals Using SQL Server CROSS JOINs

Join More Than 2 Tables

This example shows that you can Join more than 2 tables by adding additional join clauses. This is used when you are trying to combine data from several different tables. Here is the syntax:

--6) More Than 2 Tables Join
SELECT *
FROM dbo.TableA as a -- First Table
   INNER JOIN dbo.TableB as b -- Second Table 
   	ON a.Col1 = b.Col1
   INNER JOIN dbo.TableC as c -- Third Table 
   	ON a.Col1 = c.Col1;
GO

Here is the result set:

To learn more, check out Join 3 Tables in SQL

Joining on Multiple Columns

This example shows join conditions that require multiple columns in the join Clause. This is typically required when the table in the joins have multi-column primary or foreign keys. Here is the syntax:

--7) Multi-Column Joins
SELECT *
FROM dbo.TableD as a 
   INNER JOIN dbo.TableE as b 
   	ON a.Col1 = b.Col1 
   	AND a.Col2 = b.Col2;
GO

Here is the result set:

To learn more, check out Learn about SQL Joins on Multiple Columns

Next Steps

• Be sure to bookmark this tutorial to use as a quick reference to the different types of joins.
• Check out these additional MSSQLTips.com tips and tutorials:
  • All SQL JOIN Tips
  • T-SQL Tips
  • SQL Self Join
  • SQL Server SELECT Examples
  • SQL SELECT Tutorial
  • Compare SQL Server Datasets with INTERSECT and EXCEPT

About the author

Jim Evans is an IT Manager currently for Crowe who has managed DBA, Developer, BI and Data Management teams for over 20 years.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips