🧚 Listen! Beekeeper Studio is a fast, modern, and open source SQL Server GUI Download
May 19, 2025 By Matthew Rathbone

Joining tables is one of the most important concepts in SQL Server, allowing you to combine data from multiple tables to create meaningful reports and analyses. Whether you’re building complex reports or performing data analysis, understanding how joins work is essential for any SQL developer.

In this comprehensive guide, we’ll walk through each type of join in SQL Server with practical examples, clear explanations, and real-world scenarios.

🔖 Quick Reference: Need a quick lookup? Check out our SQL JOIN Cheat Sheet with all JOIN types, visual diagrams, and code examples in one place.

Understanding SQL Server Joins

SQL Server joins are operations that combine rows from two or more tables based on a related column between them. The most common join types are:

  • INNER JOIN: Returns only matching records from both tables
  • LEFT JOIN: Returns all records from the left table, plus matching records from the right table
  • RIGHT JOIN: Returns all records from the right table, plus matching records from the left table
  • FULL OUTER JOIN: Returns all records when there’s a match in either table

Setting Up Sample Data

For our examples, we’ll use two simple tables that represent a common business scenario:

Orders Table:

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    Product NVARCHAR(50),
    OrderDate DATE
);

INSERT INTO Orders VALUES 
(1, 1, 'Laptop', '2024-01-15'),
(2, 2, 'Mouse', '2024-01-16'),
(3, 3, 'Keyboard', '2024-01-17');
OrderID CustomerID Product OrderDate
1 1 Laptop 2024-01-15
2 2 Mouse 2024-01-16
3 3 Keyboard 2024-01-17

Customers Table:

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Name NVARCHAR(50),
    City NVARCHAR(50)
);

INSERT INTO Customers VALUES 
(1, 'John Doe', 'New York'),
(2, 'Jane Smith', 'Los Angeles'),
(4, 'Alex Johnson', 'Chicago');
CustomerID Name City
1 John Doe New York
2 Jane Smith Los Angeles
4 Alex Johnson Chicago

INNER JOIN

An INNER JOIN returns only the rows that have a matching value in both tables. This is the most commonly used type of join and is perfect when you only want to see records that exist in both tables.

When to Use INNER JOIN

  • When you need data that exists in both tables
  • For reports showing complete relationships
  • When excluding incomplete records is desired

SQL Query Example

SELECT 
    o.OrderID,
    c.Name,
    c.City,
    o.Product,
    o.OrderDate
FROM Orders o
INNER JOIN Customers c ON o.CustomerID = c.CustomerID;

Result

OrderID Name City Product OrderDate
1 John Doe New York Laptop 2024-01-15
2 Jane Smith Los Angeles Mouse 2024-01-16

Key Points:

  • Only 2 rows returned (OrderID 3 excluded because CustomerID 3 doesn’t exist in Customers table)
  • Only customers who have placed orders appear in the result
  • The join condition (o.CustomerID = c.CustomerID) determines which rows match

LEFT JOIN

A LEFT JOIN (also called LEFT OUTER JOIN) returns all rows from the left table, plus any matching rows from the right table. When there’s no match, NULL values appear for columns from the right table.

When to Use LEFT JOIN

  • When you need all records from the main table (left table)
  • For reports showing data gaps or missing relationships
  • When you want to find unmatched records

SQL Query Example

SELECT 
    o.OrderID,
    c.Name,
    c.City,
    o.Product,
    o.OrderDate
FROM Orders o
LEFT JOIN Customers c ON o.CustomerID = c.CustomerID;

Result

OrderID Name City Product OrderDate
1 John Doe New York Laptop 2024-01-15
2 Jane Smith Los Angeles Mouse 2024-01-16
3 NULL NULL Keyboard 2024-01-17

Key Points:

  • All 3 orders appear in the result (preserves all left table data)
  • OrderID 3 shows NULL for customer information because CustomerID 3 doesn’t exist in the Customers table
  • Useful for identifying orders without valid customer data

RIGHT JOIN

A RIGHT JOIN (also called RIGHT OUTER JOIN) returns all rows from the right table, plus any matching rows from the left table. When there’s no match, NULL values appear for columns from the left table.

When to Use RIGHT JOIN

  • When you need all records from the right table
  • Less common than LEFT JOIN (you can usually rewrite as LEFT JOIN)
  • Useful when the “main” data is in the second table

SQL Query Example

SELECT 
    o.OrderID,
    c.Name,
    c.City,
    o.Product,
    o.OrderDate
FROM Orders o
RIGHT JOIN Customers c ON o.CustomerID = c.CustomerID;

Result

OrderID Name City Product OrderDate
1 John Doe New York Laptop 2024-01-15
2 Jane Smith Los Angeles Mouse 2024-01-16
NULL Alex Johnson Chicago NULL NULL

Key Points:

  • All 3 customers appear in the result (preserves all right table data)
  • Alex Johnson shows NULL for order information because he hasn’t placed any orders
  • Useful for identifying customers who haven’t made any purchases

FULL OUTER JOIN

A FULL OUTER JOIN (or just FULL JOIN) returns all rows when there’s a match in either table. It combines the results of both LEFT and RIGHT joins, showing NULL values where there’s no match on either side.

When to Use FULL OUTER JOIN

  • When you need all records from both tables
  • For comprehensive data analysis
  • When identifying all relationships and gaps between tables

SQL Query Example

SELECT 
    o.OrderID,
    c.Name,
    c.City,
    o.Product,
    o.OrderDate
FROM Orders o
FULL OUTER JOIN Customers c ON o.CustomerID = c.CustomerID;

Result

OrderID Name City Product OrderDate
1 John Doe New York Laptop 2024-01-15
2 Jane Smith Los Angeles Mouse 2024-01-16
3 NULL NULL Keyboard 2024-01-17
NULL Alex Johnson Chicago NULL NULL

Key Points:

  • All 4 possible rows appear in the result
  • Shows both orphaned orders (OrderID 3) and customers without orders (Alex Johnson)
  • Combines the comprehensive view of both LEFT and RIGHT joins
  • Most complete picture of the relationship between tables

Key Takeaways

Understanding SQL Server joins is essential for effective database querying. Here’s a quick reference:

Join Type Returns Use Case
INNER JOIN Only matching records Most common; when you need complete relationships
LEFT JOIN All left + matching right When left table is primary; finding missing right data
RIGHT JOIN All right + matching left Less common; when right table is primary
FULL OUTER JOIN All records from both Complete analysis; finding all relationships and gaps

Best Practices

  1. Use table aliases (o for Orders, c for Customers) to make queries cleaner
  2. Start with INNER JOIN if you’re unsure, then adjust based on requirements
  3. Use LEFT JOIN more frequently than RIGHT JOIN for better readability
  4. Always specify the join condition with ON clause
  5. Consider performance - joins on indexed columns are faster

Practice Tips

To master SQL Server joins:

  1. Start with small datasets like the examples above
  2. Draw Venn diagrams to visualize what each join returns
  3. Experiment with different table structures and relationships
  4. Use SQL Server Management Studio or Beekeeper Studio to practice
  5. Test edge cases like NULL values and missing relationships

Remember: joins are fundamental to relational databases. Once you master them, you’ll be able to build powerful queries that extract meaningful insights from your data.

Beekeeper Studio Is A Free & Open Source SQL Server GUI

I’ve upgraded from SQL Server and it was a smooth change — Beekeeper Studio enhanced my own workstream, offering a cleaner experience and better performance, as well as flexibility. - Aman Bhattarai

Beekeeper Studio is a fast, intuitive and easy to use SQL Server client. It supports loads of other databases too, and works great on Windows, Mac and Linux.

Beekeeper's Linux version is 100% full-featured, no cut corners, no feature compromises.

What SQL Server Users Say About Beekeeper Studio

Mark Koops

So far the most comprehensive DB admin GUI out there. The only reason I haven’t given 5 stars is I cannot claim to have used it exhaustively only having discovered it two weeks ago. But so far, mySQL, MSSQL and Postgres in one tool is simply incredible.
Planning to purchase once pay day hits.

Neal

I had been using the free version of Beekeeper studio for a while, but when I needed to work with some MongoDB databases I upgraded to the paid version. And honestly, I was up and running in no time. Beekeeper is slick, fast, and it just works. Very intuitive, very easy to use. I work with MongoDB, Postgresql, SQL Server, and other databases in my job, and having one tool that can talk to them all is just wonderful.
Thank you, Matthew and team!

Ready to Improve Your SQL Server Workflow?

download Download Free