Inner Join - different scenario examples

 Here are some tricky examples of INNER JOIN usage in SQL Server that can help you understand its versatility and complexity in different scenarios:

1. Self Join

A self join is a join where a table is joined with itself. It is useful for comparing rows within the same table.

-- Example: Find all employees who have the same manager.
SELECT e1.EmployeeID, e1.EmployeeName, e2.EmployeeName AS ManagerName
FROM Employees e1
INNER JOIN Employees e2
ON e1.ManagerID = e2.EmployeeID;

2. Joining Multiple Tables

Joining multiple tables in a single query to fetch data from different sources.

-- Example: Fetch order details along with customer and product information.
SELECT 
    Orders.OrderID, 
    Customers.CustomerName, 
    Products.ProductName, 
    OrderDetails.Quantity
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID
INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID
INNER JOIN Products ON OrderDetails.ProductID = Products.ProductID;

3. Join with Aggregates

Using INNER JOIN with aggregate functions to get summarized data.

-- Example: Get the total sales for each product.
SELECT 
    Products.ProductName, 
    SUM(OrderDetails.Quantity * OrderDetails.UnitPrice) AS TotalSales
FROM OrderDetails
INNER JOIN Products ON OrderDetails.ProductID = Products.ProductID
GROUP BY Products.ProductName;

4. Join with a Subquery

Using a subquery within an INNER JOIN to filter data.

-- Example: Get the details of customers who have placed orders in the last 30 days.
SELECT 
    Customers.CustomerID, 
    Customers.CustomerName, 
    Orders.OrderID, 
    Orders.OrderDate
FROM Customers
INNER JOIN 
    (SELECT OrderID, CustomerID, OrderDate 
     FROM Orders 
     WHERE OrderDate >= DATEADD(DAY, -30, GETDATE())) RecentOrders
ON Customers.CustomerID = RecentOrders.CustomerID;

5. Join with Conditional Logic

Using CASE statements in INNER JOIN to handle conditional logic.

-- Example: Get the order status for each order with a custom status description.
SELECT 
    Orders.OrderID, 
    CASE 
        WHEN Orders.Status = 1 THEN 'Pending'
        WHEN Orders.Status = 2 THEN 'Shipped'
        WHEN Orders.Status = 3 THEN 'Cancelled'
        ELSE 'Unknown'
    END AS OrderStatus
FROM Orders
INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID;

6. Joining Tables with Composite Keys

Joining tables on multiple columns.

-- Example: Match order details with shipments using composite keys.
SELECT 
    Orders.OrderID, 
    Orders.OrderDate, 
    Shipments.ShipmentDate
FROM Orders
INNER JOIN Shipments 
ON Orders.OrderID = Shipments.OrderID AND Orders.CustomerID = Shipments.CustomerID;

7. Join with Non-Equi Join Conditions

Using INNER JOIN with non-equality conditions.

-- Example: Get products that were ordered more than once in a single order.
SELECT 
    Products.ProductName, 
    OrderDetails.OrderID, 
    OrderDetails.Quantity
FROM Products
INNER JOIN OrderDetails ON Products.ProductID = OrderDetails.ProductID
WHERE OrderDetails.Quantity > 1;

8. Joining Tables with Null Values

Handling null values in joins.

-- Example: Get customers and their orders, considering customers who might not have any orders.
SELECT 
    Customers.CustomerName, 
    Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE Orders.OrderID IS NOT NULL;

9. Joining Tables with Aliases

Using table aliases to simplify complex joins.

-- Example: Get employee details along with their department names.
SELECT 
    e.EmployeeName, 
    d.DepartmentName
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID;

10. Join with Complex Conditions

Using complex conditions in the ON clause.

-- Example: Get all products with their suppliers, but only if the supplier is from a specific country.
SELECT 
    Products.ProductName, 
    Suppliers.SupplierName
FROM Products
INNER JOIN Suppliers ON Products.SupplierID = Suppliers.SupplierID
AND Suppliers.Country = 'USA';

These examples illustrate how INNER JOIN can be used in various complex scenarios, making it a powerful tool in SQL for combining and querying related data from multiple tables.