SQL Joins: INNER, LEFT, RIGHT, FULL

In relational databases, data is normalized into multiple tables to reduce redundancy and improve integrity. For example, a sales database might have separate tables for Customers, Orders, and Products. To answer questions like "list all customers with their order totals," you must combine rows from these tables. SQL joins provide the mechanism to combine rows from two or more tables based on a related column (typically a foreign key). The DP-900 exam expects you to understand the four fundamental join types: INNER, LEFT OUTER, RIGHT OUTER, and FULL OUTER. (The keyword OUTER is optional and often omitted.)

When a SQL query with a join executes, the database engine follows a logical processing order. First, it identifies the source tables and applies any filters (WHERE clause) that can be pushed down. Then, for each row in the first table (often called the left table or driving table), the engine looks up matching rows in the second table (right table) based on the join condition specified in the ON clause. The join condition is usually an equality comparison, like Orders.CustomerID = Customers.CustomerID.

The join condition is specified in the ON clause. It can involve multiple columns (compound join) and can use operators other than equality (non-equi joins), though the DP-900 exam focuses on equi-joins. When a join produces NULLs for unmatched rows, those NULLs can affect subsequent filtering. For example, if you write:

SELECT *
FROM Customers C
LEFT JOIN Orders O ON C.CustomerID = O.CustomerID
WHERE O.OrderDate = '2023-01-01'

This effectively turns the LEFT JOIN into an INNER JOIN because the WHERE clause filters out rows where O.OrderDate is NULL (unmatched customers). To preserve all customers, move the condition to the ON clause:

SELECT *
FROM Customers C
LEFT JOIN Orders O ON C.CustomerID = O.CustomerID AND O.OrderDate = '2023-01-01'

This nuance is frequently tested on the DP-900 exam.

In Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics dedicated SQL pools, joins are used in T-SQL queries. There is no special configuration for joins — they are part of the SQL language. However, performance depends on indexes. For large tables, an index on the join column(s) can dramatically speed up the lookup. You can verify the execution plan using:

SET SHOWPLAN_XML ON;
GO
SELECT ... FROM ... JOIN ...;
GO
SET SHOWPLAN_XML OFF;

In Azure Synapse Serverless SQL pool, joins work similarly but with distributed query processing. The DP-900 exam does not require deep knowledge of execution plans, but you should understand that joins can be expensive and that indexing is important.

Visualize two sets: A (left table rows) and B (right table rows). The join condition defines a relationship. - INNER JOIN = A ∩ B (intersection) - LEFT JOIN = A ∪ (A ∩ B) — all of A, plus the intersection (but since intersection is already part of A, it's just A with B's attributes where they match) - FULL JOIN = A ∪ B (union)

This set analogy helps but is imperfect because the join can produce multiple rows from a single left row.

The DP-900 exam does not require you to choose a join algorithm, but you should know that joins can be resource-intensive and that indexing helps.

| Join Type | Left Table Rows | Right Table Rows | NULLs from Right | NULLs from Left | |-----------|----------------|-----------------|------------------|-----------------| | INNER | Only matched | Only matched | No | No | | LEFT | All | Only matched | Yes for unmatched| No | | RIGHT | Only matched | All | No | Yes for unmatched| | FULL | All | All | Yes for unmatched| Yes for unmatched|

This table is exam gold. Memorize it.

Assume tables:

CREATE TABLE Employees (EmpID INT, Name VARCHAR(50), DeptID INT);
CREATE TABLE Departments (DeptID INT, DeptName VARCHAR(50));

Sample data:

Employees: (1, 'Alice', 10), (2, 'Bob', 20), (3, 'Charlie', NULL) Departments: (10, 'Sales'), (20, 'Marketing'), (30, 'HR')

INNER JOIN:

SELECT E.Name, D.DeptName
FROM Employees E
INNER JOIN Departments D ON E.DeptID = D.DeptID;

Result: Alice-Sales, Bob-Marketing. Charlie is excluded (DeptID is NULL, no match). HR is excluded (no employee).

LEFT JOIN:

SELECT E.Name, D.DeptName
FROM Employees E
LEFT JOIN Departments D ON E.DeptID = D.DeptID;

Result: Alice-Sales, Bob-Marketing, Charlie-NULL. Charlie preserved with NULL for DeptName.

RIGHT JOIN:

SELECT E.Name, D.DeptName
FROM Employees E
RIGHT JOIN Departments D ON E.DeptID = D.DeptID;

Result: Alice-Sales, Bob-Marketing, NULL-HR. HR preserved with NULL for Name.

FULL JOIN:

SELECT E.Name, D.DeptName
FROM Employees E
FULL JOIN Departments D ON E.DeptID = D.DeptID;

Result: Alice-Sales, Bob-Marketing, Charlie-NULL, NULL-HR. All rows from both tables appear.

Not a join type commonly tested in DP-900, but worth noting: CROSS JOIN produces every combination of rows from both tables. It has no ON clause. For example, 3 employees × 3 departments = 9 rows. This is rarely used in practice but can appear in exam questions about join fundamentals.

A table can be joined to itself. This is used for hierarchical data (e.g., employees and managers). The syntax is the same, but table aliases are essential. For example:

SELECT E1.Name AS Employee, E2.Name AS Manager
FROM Employees E1
LEFT JOIN Employees E2 ON E1.ManagerID = E2.EmpID;

This is a common pattern for reporting structures.

You can join more than two tables. The order of joins matters logically but not performance-wise (the optimizer reorders). Example:

SELECT C.CustomerName, O.OrderDate, P.ProductName
FROM Customers C
INNER JOIN Orders O ON C.CustomerID = O.CustomerID
INNER JOIN OrderDetails OD ON O.OrderID = OD.OrderID
INNER JOIN Products P ON OD.ProductID = P.ProductID;

This returns only customers who have placed orders with details.

NULL is not a value; it represents missing data. In join comparisons, NULL = NULL is not true (it yields UNKNOWN). Therefore, rows with NULL in the join column will never match any row, even if the other table has NULL. This is why Charlie (DeptID = NULL) does not match Department 30 (DeptID = 30) — they are not equal, and NULL is not equal to anything. This is a common exam trap.

This objective covers querying relational data in Azure. You should be able to write and interpret queries that use joins. The exam may present a scenario and ask which join type produces the desired result. It may also ask about the number of rows returned or the presence of NULLs.

Mastering joins requires understanding the set preservation rules and the effect of NULLs. Practice writing queries with sample data to see the results. The DP-900 exam will test your ability to choose the correct join type based on a business requirement. Remember: LEFT preserves left, RIGHT preserves right, INNER keeps only matches, FULL keeps everything.

A retail company uses Azure SQL Database to store customer and order data. The marketing team needs a report of all customers and their total order amounts, including customers who have never placed an order. The naive query uses INNER JOIN:

SELECT C.CustomerID, C.CustomerName, SUM(O.OrderAmount) AS TotalSpent
FROM Customers C
INNER JOIN Orders O ON C.CustomerID = O.CustomerID
GROUP BY C.CustomerID, C.CustomerName;

This returns only customers with orders, missing new or inactive customers. The correct query uses LEFT JOIN:

SELECT C.CustomerID, C.CustomerName, COALESCE(SUM(O.OrderAmount), 0) AS TotalSpent
FROM Customers C
LEFT JOIN Orders O ON C.CustomerID = O.CustomerID
GROUP BY C.CustomerID, C.CustomerName;

COALESCE replaces NULL with 0. In production, this query runs daily against millions of rows. Without an index on Orders.CustomerID, the join performs a full table scan, taking minutes. Adding a nonclustered index reduces it to seconds. Misconfiguration (e.g., missing index) leads to timeout errors and frustrated business users.

A multinational corporation uses Azure Synapse Analytics for HR data. They need a complete list of all employees and their department names, including employees not yet assigned to a department (DeptID is NULL) and departments with no employees. This requires a FULL JOIN:

SELECT E.EmployeeName, D.DepartmentName
FROM Employees E
FULL JOIN Departments D ON E.DeptID = D.DeptID;

In a data warehouse with billions of rows, FULL JOIN can be extremely expensive because it requires preserving both sides and handling NULLs. Often, the business requirement can be satisfied with a LEFT JOIN plus a separate query for empty departments, then UNION. Misusing FULL JOIN unnecessarily can cause query timeouts and resource contention. The data engineering team must evaluate whether the full outer join is truly needed.

An e-commerce platform uses Azure SQL Database to track products and inventory. The reporting team needs to list all products and their current stock quantity, including products that have never been stocked (no inventory record). This is a LEFT JOIN from Products to Inventory. However, a common mistake is to filter on inventory columns in the WHERE clause:

SELECT P.ProductID, P.ProductName, I.Quantity
FROM Products P
LEFT JOIN Inventory I ON P.ProductID = I.ProductID
WHERE I.Quantity > 0;

This eliminates products with NULL quantity (unstocked products), turning the join into an INNER JOIN. The correct approach is to move the condition to the ON clause or use a subquery. In production, this error led to a report that omitted new products, causing inventory managers to believe they had no new items to order. The fix was to change the WHERE to WHERE (I.Quantity > 0 OR I.Quantity IS NULL) or to use ON P.ProductID = I.ProductID AND I.Quantity > 0.