Joins and Views
Joins combine rows from multiple tables based on related keys. Views encapsulate query logic for reuse, security, and abstraction. Master both to write maintainable T-SQL for reporting and application backends.
INNER JOIN
Returns rows where the join condition matches in both tables:
SELECT
u.Name,
o.OrderID,
o.Total,
o.CreatedAt
FROM dbo.Users u
INNER JOIN dbo.Orders o ON u.UserID = o.UserID
WHERE o.Total > 100
ORDER BY o.CreatedAt DESC;
Only users with at least one order over $100 appear. Users without qualifying orders are excluded.
LEFT OUTER JOIN
Keeps all rows from the left table; NULLs where no match on the right:
SELECT
u.UserID,
u.Name,
COUNT(o.OrderID) AS order_count,
ISNULL(SUM(o.Total), 0) AS lifetime_spend
FROM dbo.Users u
LEFT JOIN dbo.Orders o ON u.UserID = o.UserID
GROUP BY u.UserID, u.Name;
Users with zero orders show order_count = 0.
RIGHT and FULL OUTER JOIN
-- Rare in practice — LEFT JOIN with table order swapped is clearer
SELECT p.ProductID, p.Name, s.StockLevel
FROM dbo.Products p
FULL OUTER JOIN dbo.Inventory s ON p.ProductID = s.ProductID
WHERE p.ProductID IS NULL OR s.ProductID IS NULL; -- mismatches only
FULL OUTER finds orphans on either side — useful for data quality audits.
CROSS JOIN
Cartesian product — every row paired with every row:
SELECT d.Date, p.ProductID
FROM dbo.DateDimension d
CROSS JOIN dbo.Products p;
Use deliberately for calendars or combinations — accidental CROSS JOINs explode result sets.
CROSS APPLY and OUTER APPLY
Run a correlated subquery per outer row:
-- Top order per user
SELECT u.Name, top_order.OrderID, top_order.Total
FROM dbo.Users u
CROSS APPLY (
SELECT TOP 1 OrderID, Total
FROM dbo.Orders
WHERE UserID = u.UserID
ORDER BY Total DESC
) AS top_order;
OUTER APPLY preserves users with no orders (NULL columns). APPLY often beats correlated subqueries for top-N-per-group.
Self Join
SELECT
e.Name AS employee,
m.Name AS manager
FROM dbo.Employees e
LEFT JOIN dbo.Employees m ON e.ManagerID = m.EmployeeID;
Hierarchy queries may use recursive CTEs for multi-level org charts.
Subqueries
-- Scalar subquery in SELECT
SELECT
u.Name,
(SELECT COUNT(*) FROM dbo.Orders o WHERE o.UserID = u.UserID) AS order_count
FROM dbo.Users u;
-- IN subquery
SELECT * FROM dbo.Products
WHERE CategoryID IN (SELECT CategoryID FROM dbo.Categories WHERE IsActive = 1);
-- EXISTS — often faster than IN for large sets
SELECT u.Name
FROM dbo.Users u
WHERE EXISTS (
SELECT 1 FROM dbo.Orders o
WHERE o.UserID = u.UserID AND o.Total > 500
);
EXISTS stops at first match — good for semi-join patterns.
Common Table Expressions (CTEs)
WITH recent_orders AS (
SELECT OrderID, UserID, Total, CreatedAt,
ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY CreatedAt DESC) AS rn
FROM dbo.Orders
)
SELECT u.Name, r.OrderID, r.Total, r.CreatedAt
FROM recent_orders r
INNER JOIN dbo.Users u ON r.UserID = u.UserID
WHERE r.rn = 1;
CTEs improve readability — not automatically materialized unless indexed or referenced multiple times with optimization hints.
Creating Views
CREATE OR ALTER VIEW dbo.vw_ActiveUsers
AS
SELECT UserID, Name, Email, CreatedAt
FROM dbo.Users
WHERE Status = N'active';
GO
SELECT * FROM dbo.vw_ActiveUsers WHERE CreatedAt >= '2024-01-01';
Views do not store data (except indexed views) — they are saved SELECT statements.
Security Through Views
CREATE VIEW dbo.vw_UserPublicProfile
AS
SELECT UserID, Name, CreatedAt FROM dbo.Users;
-- Deny direct table access; grant SELECT on view only
Indexed Views (Materialized)
CREATE VIEW dbo.vw_OrderSummary
WITH SCHEMABINDING
AS
SELECT
CustomerID,
COUNT_BIG(*) AS order_count,
SUM(Total) AS total_spent
FROM dbo.Orders
GROUP BY CustomerID;
GO
CREATE UNIQUE CLUSTERED INDEX IX_vw_OrderSummary
ON dbo.vw_OrderSummary (CustomerID);
Requirements: SCHEMABINDING, deterministic functions, COUNT_BIG for aggregates. Enterprise/Developer for some features; Standard has restrictions.
Join Performance Tips
| Pattern | Tip |
|---|---|
| Missing index on FK | Add nonclustered index on join columns |
| Implicit conversion | Match types — INT join to VARCHAR kills seeks |
| OR in joins | Rewrite as UNION ALL when possible |
| Functions on columns | WHERE YEAR(d)=2024 — use range: d >= '2024-01-01' AND d < '2025-01-01' |
| Large hash joins | Memory grant warnings — update statistics |
-- Check plan for join type: Nested Loop, Merge, Hash
SET STATISTICS IO ON;
-- Run query with Actual Execution Plan
Common Mistakes
| Mistake | Result |
|---|---|
| INNER JOIN when LEFT needed | Silent row loss |
| Duplicate rows from join | Missing or wrong key — accidental multiplication |
View with SELECT * |
Breaks when underlying schema changes |
| Non-deterministic view for index | Indexed view creation fails |
| Filtering on outer table in WHERE vs ON | Changes LEFT JOIN semantics |
-- Wrong — converts LEFT JOIN to INNER behavior
SELECT *
FROM Users u
LEFT JOIN Orders o ON u.UserID = o.UserID
WHERE o.Status = N'Paid'; -- Filters NULL orders out
-- Right — filter in ON or use subquery
SELECT *
FROM Users u
LEFT JOIN Orders o ON u.UserID = o.UserID AND o.Status = N'Paid';
Production Scenario: Read Replica Reporting View
CREATE OR ALTER VIEW dbo.vw_DailySales
AS
SELECT
CAST(CreatedAt AS DATE) AS sale_date,
COUNT(*) AS order_count,
SUM(Total) AS revenue
FROM dbo.Orders
WHERE Status = N'Paid'
GROUP BY CAST(CreatedAt AS DATE);
Point BI tools at the view on a readable AG secondary with ApplicationIntent=ReadOnly.
Troubleshooting Slow Joins
- Capture Actual Execution Plan
- Check estimated vs actual row counts
- Update statistics:
UPDATE STATISTICS dbo.Orders WITH FULLSCAN - Verify indexes on join and filter columns
- Consider filtered indexes for skewed predicates
SELECT
s.name AS schema_name,
t.name AS table_name,
i.name AS index_name,
i.type_desc
FROM sys.indexes i
INNER JOIN sys.tables t ON i.object_id = t.object_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.name IN (N'Orders', N'Users');
Views simplify application queries; joins power them — design keys and indexes together for predictable performance.