INNER JOIN: Matching Rows Only
An INNER JOIN returns only rows that have matching values in both tables based on the join predicate. SELECT o.OrderID, c.CustomerName FROM Sales.Orders o INNER JOIN Sales.Customer c ON o.CustomerID = c.CustomerID pulls back every order that has a corresponding customer record, silently dropping orders with a CustomerID that doesn't exist in the Customer table (an orphaned foreign key) and dropping customers with no orders at all.
Cricket analogy: An INNER JOIN between a Players table and a MatchInnings table is like a stats page that only shows players who actually batted in that match — anyone on the squad list who didn't play, and any innings record with a corrupted player ID, simply disappears from the report.
OUTER JOIN: Keeping Unmatched Rows
A LEFT OUTER JOIN keeps every row from the left table regardless of a match, filling unmatched columns from the right table with NULL: SELECT c.CustomerName, o.OrderID FROM Sales.Customer c LEFT JOIN Sales.Orders o ON c.CustomerID = o.CustomerID surfaces every customer, with NULL in OrderID for anyone who has never ordered. RIGHT JOIN is the mirror image, and FULL OUTER JOIN keeps unmatched rows from both sides simultaneously.
Cricket analogy: A LEFT JOIN from Squad to MatchInnings lists every player in the squad, including a benched twelfth man who never batted, showing NULL for his runs — nobody is dropped from the roster the way INNER JOIN would drop him.
SELECT
c.CustomerName,
o.OrderID,
o.OrderDate
FROM Sales.Customer c
LEFT JOIN Sales.Orders o
ON c.CustomerID = o.CustomerID
WHERE o.OrderID IS NULL; -- customers who have never placed an orderFiltering on a right-table column in the WHERE clause of a LEFT JOIN (e.g. WHERE o.Status = 'Shipped') silently converts it back into an INNER JOIN, because rows with NULL never satisfy that predicate. Move such conditions into the ON clause if you want to preserve unmatched left-side rows.
CROSS JOIN and Self Joins
A CROSS JOIN produces the Cartesian product of two tables — every row from the first paired with every row from the second — and is used deliberately for tasks like generating a date-times-store combination matrix, but is a common accidental bug when a join predicate is forgotten. A self join joins a table to itself using two aliases, typically to compare rows within the same table, such as finding employees who share a manager: SELECT e1.Name, e2.Name AS ManagerName FROM Employee e1 JOIN Employee e2 ON e1.ManagerID = e2.EmployeeID.
Cricket analogy: A CROSS JOIN of every batsman with every bowler in an IPL season generates a full matchup matrix for analytics, useful on purpose, while forgetting the ON clause in a batsman-to-team join accidentally creates that same explosive combination.
A self join always requires table aliases (e.g. e1, e2) because SQL Server cannot otherwise distinguish which instance of the repeated table a column reference belongs to.
- INNER JOIN returns only rows with matches in both tables.
- LEFT/RIGHT OUTER JOIN preserves unmatched rows from one side, filling the other side with NULL.
- FULL OUTER JOIN preserves unmatched rows from both sides.
- Filtering right-table columns in WHERE on a LEFT JOIN silently turns it into an INNER JOIN — use ON instead.
- CROSS JOIN produces a Cartesian product and is dangerous when accidental (missing ON clause).
- Self joins compare rows within the same table using two distinct aliases.
- Always qualify column names with table aliases once more than one table is involved.
Practice what you learned
1. What happens to unmatched rows from the left table in a LEFT OUTER JOIN?
2. Why does adding WHERE o.Status = 'Shipped' to a LEFT JOIN query often behave like an INNER JOIN?
3. What does a CROSS JOIN between a 100-row table and a 50-row table produce (with no filter)?
4. What must a self join always use to distinguish the two references to the same table?
5. In FULL OUTER JOIN, which rows appear in the result?
Was this page helpful?
You May Also Like
SELECT and Filtering
Learn how to retrieve exactly the rows and columns you need from SQL Server tables using SELECT, WHERE, and related filtering clauses.
Aggregations and Grouping
Master aggregate functions and GROUP BY/HAVING to summarize rows into meaningful totals, averages, and counts in SQL Server.
Subqueries and CTEs
Learn how to nest queries and use Common Table Expressions to break complex T-SQL logic into readable, reusable, and recursive building blocks.
Related Reading
Related Study Notes in Programming
Browse all study notesApache Spark Study Notes
Programming · 30 topics
ProgrammingApache Flink Study Notes
Programming · 30 topics
ProgrammingHadoop Study Notes
Programming · 30 topics
ProgrammingSnowflake Study Notes
Programming · 30 topics
ProgrammingApache Airflow Study Notes
Programming · 30 topics
Programmingdbt (Data Build Tool) Study Notes
Programming · 30 topics