Mastering SQL JOINs in Fabric Warehouse INNER, LEFT, RIGHT, FULL, CROSS | Microsoft Fabric Tutorial

Mastering SQL JOINs in Fabric Warehouse – INNER, LEFT, RIGHT, FULL, CROSS

In this Microsoft Fabric tutorial, you’ll learn how to use different types of SQL JOINs to combine data across tables inside Fabric Warehouse. We’ll walk through INNER, LEFT, RIGHT, FULL OUTER, and CROSS JOIN with clear examples.

📖 What is a SQL JOIN?

A JOIN clause is used to combine rows from two or more tables based on a related column between them. JOINs allow you to query relational data stored in separate tables in a unified view.

📚 Types of JOINs in Fabric Warehouse

• INNER JOIN – Returns only the matching rows from both tables
• LEFT JOIN – Returns all rows from the left table and matched rows from the right
• RIGHT JOIN – Returns all rows from the right table and matched rows from the left
• FULL OUTER JOIN – Returns all rows from both tables, matched or not
• CROSS JOIN – Returns a Cartesian product (all combinations of rows)

🧱 Step 1: Create Tables

CREATE TABLE dbo.Customers (
    CustomerID INT,
    CustomerName VARCHAR(50),
    Country VARCHAR(50)
);

CREATE TABLE dbo.Orders (
    OrderID INT,
    CustomerID INT,
    OrderAmount DECIMAL(10,2),
    OrderDate DATE
);

🧪 Step 2: Insert Sample Data

INSERT INTO dbo.Customers (CustomerID, CustomerName, Country) VALUES
(1, 'Aamir', 'USA'),
(2, 'Sara', 'Canada'),
(3, 'John', 'UK'),
(4, 'Mia', 'Australia');

INSERT INTO dbo.Orders (OrderID, CustomerID, OrderAmount, OrderDate) VALUES
(101, 1, 500.00, '2024-01-01'),
(102, 2, 700.00, '2024-01-02'),
(103, 1, 300.00, '2024-01-05'),
(104, 5, 250.00, '2024-01-07');  -- CustomerID 5 does not exist in Customers

🔎 Step 3: SQL JOIN Examples

✅ INNER JOIN

SELECT c.CustomerName, o.OrderID, o.OrderAmount
FROM dbo.Customers c
INNER JOIN dbo.Orders o ON c.CustomerID = o.CustomerID;

✅ LEFT JOIN

SELECT c.CustomerName, o.OrderID, o.OrderAmount
FROM dbo.Customers c
LEFT JOIN dbo.Orders o ON c.CustomerID = o.CustomerID;

✅ RIGHT JOIN

SELECT c.CustomerName, o.OrderID, o.OrderAmount
FROM dbo.Customers c
RIGHT JOIN dbo.Orders o ON c.CustomerID = o.CustomerID;

✅ FULL OUTER JOIN

SELECT c.CustomerName, o.OrderID, o.OrderAmount
FROM dbo.Customers c
FULL OUTER JOIN dbo.Orders o ON c.CustomerID = o.CustomerID;

✅ CROSS JOIN (Bonus)

SELECT c.CustomerName, o.OrderID
FROM dbo.Customers c
CROSS JOIN dbo.Orders o;

🎬 Watch the Full Tutorial

Blog post written with the help of ChatGPT.