Lesson 11

Selecting Data in SQL Server: Module Conclusion

This module developed the skills needed to turn stored data into useful answers. Starting with a single-table SELECT, you progressed through joins, aliases, unions, additional query keywords, aggregate functions, grouping, and subqueries. Together, these techniques let you answer questions about relationships, missing information, totals, and qualifying records without assembling every result in application code.

The examples in this conclusion use the SQL Server 2025 course context. Most of the core patterns also apply to earlier supported SQL Server versions. More important than memorizing individual statements is understanding what each query returns: which rows qualify, whether unmatched rows survive, how duplicates behave, and what one output row represents. Those decisions determine whether a report answers the intended question.

1. Begin with the Question and the Result

The module introduction and overview of selecting data established the basic query structure. The select list identifies the columns and expressions to return; FROM identifies their sources; WHERE restricts qualifying rows. Other clauses organize, summarize, and order that result.

Before writing SQL, describe the intended output in one sentence. For example: “Return one row per employee hired during 2026, including the employee identifier, name, and hire date.” This description establishes the result's level of detail, often called its grain. A different request, such as one row per department, requires a different query even if it uses the same tables.

SELECT e.EmployeeID, e.LastName, e.HireDate
FROM dbo.Employees AS e
WHERE e.HireDate >= '20260101'
  AND e.HireDate < '20270101'
ORDER BY e.HireDate, e.EmployeeID;

This example uses a half-open date range, including the starting boundary and excluding the next year's boundary. It accommodates time values on the final day of the year. Explicit columns also make the output contract clear. Although SELECT * is useful during exploration, a maintained report should normally request only the columns its consumer needs.

A query does not always require WHERE. Returning every row can be intentional. Similarly, FROM is unnecessary when evaluating an expression such as SELECT 2 + 3;. Treat clauses as tools for expressing requirements, not mandatory decorations.

2. Choose Joins According to Which Rows Must Survive

The lessons on multiple-table retrieval concepts and join syntax explained how related tables contribute to one result. An inner join returns matching combinations. A left outer join also retains unmatched rows from its left input, supplying NULL for missing right-side values. A right outer join reverses the preserved side; a full outer join preserves unmatched rows from both inputs.

These choices express different business requirements. A list of employees who submitted timesheets needs matches. A list of all employees, including those without timesheets, needs preservation. Use the relationship keys in the join condition and check whether each employee can match one timesheet or many. One-to-many matches legitimately produce several output rows for the same employee.

SELECT e.EmployeeID, e.LastName, t.AnnualTime
FROM dbo.Employees AS e
LEFT JOIN dbo.Timesheets AS t
    ON t.EmployeeID = e.EmployeeID
   AND t.AnnualTime > 0
ORDER BY e.EmployeeID;

Here, the condition on annual time limits which timesheets qualify as matches while retaining employees without a qualifying timesheet. Moving that condition to WHERE would remove the rows whose timesheet values are NULL. This is a common reason an intended outer join unexpectedly behaves like an inner join.

Row multiplication also matters when joining several child tables. If an employee has three timesheets and two training records, joining both detail tables directly can produce six combinations. Summing time over those combinations can overstate the total. Aggregate each child source to the required grain before combining them, or use an existence test when only the presence of related records matters.

3. Use Aliases to Make Relationships Readable

The aliasing lesson introduced temporary names for table references and output expressions. A table alias such as e makes repeated references manageable. A column alias such as TotalSalary gives a calculation an understandable heading. Neither changes the underlying table definition.

Once a table reference has an alias, use that alias consistently to qualify its columns. This is especially important when several sources contain columns with the same name. In a self-join, separate aliases distinguish separate roles played by the same table, such as an employee and that employee's manager.

Alias visibility follows query scope and logical processing rules. A select-list alias can generally be used in ORDER BY, but not in the same query block's WHERE, GROUP BY, or HAVING. Repeat the expression or introduce a derived table or common table expression when a later query block needs to reference the calculated name.

4. Distinguish Combining Rows from Matching Rows

The lesson on union queries introduced a different way to combine data. A join matches rows and exposes columns from related sources. A union appends compatible result rows. Use unions when separate sources should contribute records to the same output structure, such as current and archived transactions.

SELECT EmployeeID, FirstName, LastName
FROM dbo.Employees_US
UNION ALL
SELECT EmployeeID, FirstName, LastName
FROM dbo.Employees_Canada;

Each branch must return the same number of columns, and corresponding positions must have compatible types. Position determines alignment, not the spelling of the source column names. The first branch supplies the output names. When types differ, explicit casts can make the intended conversion clearer and avoid surprises from implicit conversion rules.

UNION removes duplicate complete rows; UNION ALL retains them. Choose according to the required result. Duplicate removal can require additional work, but replacing UNION with UNION ALL changes semantics when duplicates exist. Conversely, adding UNION or DISTINCT to conceal an incorrect join does not repair the underlying relationship.

If order matters, apply ORDER BY to the final combined result. Do not assume rows from the first branch will appear before rows from the second. Also consider whether identifiers are globally unique: employee number 100 in one regional system might represent someone different from employee number 100 in another.

5. Shape Results with Deliberate Keywords

The additional-keywords lesson expanded the basic query vocabulary. DISTINCT eliminates duplicates across the selected expressions. TOP (n) limits the number of returned rows. ASC and DESC specify ordering direction. These features are useful only when their effect matches the question.

SELECT TOP (5) e.EmployeeID, e.LastName, e.Salary
FROM dbo.Employees AS e
ORDER BY e.Salary DESC, e.EmployeeID;

This asks for five employees ordered by salary, with the unique employee identifier resolving salary ties. Without ORDER BY, “top five” does not mean the five highest salaries. If the business requirement is to include everyone tied at the cutoff, investigate WITH TIES and choose its ordering expressions carefully: ties are determined by the complete ordering specification.

Keep search mechanisms distinct. LIKE performs character-pattern matching; CONTAINS is a full-text predicate used with an appropriate full-text index. Query hints supplied through OPTION influence optimization or execution behavior. They are not a substitute for a correct query, suitable indexes, or evidence from an execution plan.

The lesson also introduced newer functions. One practical detail deserves emphasis: enabling the ordinal column in STRING_SPLIT records each token's original position but does not guarantee output order. Request that order explicitly:

SELECT value, ordinal
FROM STRING_SPLIT(N'Apple,Banana,Cherry', N',', 1)
ORDER BY ordinal;

For version-specific functions, check both the SQL Server version and relevant database compatibility requirements before adopting an example. New search and document features extend the query language, while the same principles of filtering, ordering, and correct result shape continue to apply.

6. Aggregate at the Correct Level

The lessons on aggregate-query concepts and grouped queries showed how detailed rows become summaries. SUM, AVG, MIN, MAX, and COUNT answer different questions about a set of rows. An aggregate without GROUP BY can summarize the entire qualifying input; grouping produces separate summaries for each group.

COUNT(*) counts rows, while COUNT(column) counts non-NULL values. The other functions listed above ignore NULL inputs. Consequently, a missing salary is not automatically treated as zero in an average. Replacing missing values with zero is a business decision that changes the calculation, not a harmless formatting adjustment.

Group entities by their identifying keys. Two customers with the same display name should not be merged accidentally into one customer total. Include useful labels alongside their keys in the grouping expressions. Also select the grouping values when readers need them to interpret the result; SQL can permit a grouping key to be omitted from the select list, but the resulting report may be unclear.

SELECT e.DepartmentID,
       COUNT(*) AS EmployeeCount,
       SUM(e.Salary) AS TotalSalary
FROM dbo.Employees AS e
WHERE e.Salary IS NOT NULL
GROUP BY e.DepartmentID
HAVING SUM(e.Salary) > 100000
ORDER BY TotalSalary DESC, e.DepartmentID;

Here, WHERE removes employees with unknown salaries before aggregation. HAVING then retains departments whose qualifying salaries total more than 100,000. The employee count therefore counts employees with known salaries, not necessarily everyone in the department. Read column headings and filters together when explaining a report.

For multiple summary levels, ROLLUP provides hierarchical subtotals, CUBE provides grouping combinations, and GROUPING SETS requests selected levels. Use GROUPING to distinguish summary placeholders from stored NULL values. Grouping never guarantees presentation order; use an explicit ORDER BY that matches the report's needs.

7. Use Subqueries to Express Dependencies and Existence

The lesson on writing subqueries introduced queries nested inside other statements. A scalar subquery supplies one value, an IN subquery supplies values to match, and a derived table supplies a rowset in FROM. A scalar subquery must return no more than one row: zero rows produce NULL, while multiple rows cause an error.

A correlated subquery references the surrounding query. Correlation describes a logical dependency; it does not guarantee a particular physical execution strategy. SQL Server can transform query expressions during optimization. Avoid assuming that every correlated subquery necessarily performs a separate complete scan for every outer row.

SELECT e.EmployeeID, e.LastName
FROM dbo.Employees AS e
WHERE EXISTS (
    SELECT 1
    FROM dbo.Timesheets AS t
    WHERE t.EmployeeID = e.EmployeeID
)
ORDER BY e.EmployeeID;

This returns each qualifying employee once, regardless of how many matching timesheets exist, assuming the employee key is unique. A direct join would return one row for each matching combination. Therefore, a join and an existence test are not automatically interchangeable.

Use NOT EXISTS for the corresponding absence question. Be careful with NOT IN when its input can contain NULL, because an unknown comparison can exclude rows unexpectedly. Choose the formulation that expresses the requirement clearly, then assess performance with representative data and execution plans. There is no universal rule that joins outperform EXISTS.

8. Combine the Techniques in a Practical Report

Consider this request: “List every customer and the total value of their orders during 2026, including customers who placed no orders.” Assume illustrative tables dbo.Customers(CustomerID, CustomerName) and dbo.Orders(OrderID, CustomerID, OrderDate, Amount), with unique customer and order identifiers and a non-null numeric order amount. Adapt these names to your own database.

;WITH AnnualOrders AS (
    SELECT o.CustomerID,
           COUNT(*) AS OrderCount,
           SUM(o.Amount) AS TotalAmount
    FROM dbo.Orders AS o
    WHERE o.OrderDate >= '20260101'
      AND o.OrderDate < '20270101'
    GROUP BY o.CustomerID
)
SELECT c.CustomerID,
       c.CustomerName,
       COALESCE(a.OrderCount, 0) AS OrderCount,
       COALESCE(a.TotalAmount, 0) AS TotalAmount
FROM dbo.Customers AS c
LEFT JOIN AnnualOrders AS a
    ON a.CustomerID = c.CustomerID
ORDER BY TotalAmount DESC, c.CustomerID;

The common table expression first expresses annual totals at one row per customer. The outer query joins those totals to the customer list. The left join preserves customers without qualifying orders, and COALESCE displays their absent totals as zero. Because the input amount is assumed non-null, this does not conceal an unknown amount on an existing order.

Notice how the structure protects the intended grain. Grouping by customer identifier avoids merging people who share a name. Filtering dates inside the aggregate keeps the annual restriction separate from customer preservation. Ordering by total and then identifier makes ties predictable. A CTE gives this logic a name within the statement; it is not a promise that SQL Server stores or caches the intermediate result.

If the report instead required only customers whose annual total exceeded 10,000, an inner join to qualifying aggregates or an outer filter on the total would express that change. State the requirement first, then change the SQL. Do not preserve customers without orders while simultaneously applying a condition that deliberately excludes them and still describe the result as “every customer.”

9. Validate Meaning Before Tuning Performance

A successful execution proves that SQL Server accepted a statement, not that the result is correct. Test with small examples whose answers you can calculate independently. Include an unmatched parent row, several matches for one parent, duplicate display names, missing values, equal sort values, and dates exactly on the filtering boundaries.

For aggregate reports, compare a few group totals with their underlying detail rows. For unions, decide whether duplicate rows represent repeated events or redundant records. For joins, inspect the number of matches per key. For existence checks, verify that additional matching child rows do not unexpectedly increase the number of outer rows returned.

Once correctness is established, inspect the actual execution plan and relevant measurements. Useful questions include whether row estimates differ substantially from observed counts, whether joins multiply rows unexpectedly, and whether conversions interfere with useful access paths. Indexes, statistics, data distribution, and selected columns can matter more than cosmetic rewrites.

10. Place Distributed Queries in Context

The introduction mentioned distributed selection, although the supplied workflow contains no separate worked lesson on it. A distributed query accesses multiple data sources. Related terminology includes linked servers, provider interfaces, and heterogeneous sources, meaning different kinds of systems. These concepts extend the selection techniques you practiced here.

For now, recognize that remote data introduces additional connection, permission, provider, and data-transfer considerations. Querying a remote source does not remove the need to define keys, duplicates, filters, or aggregation correctly. Treat distributed access as a topic for further study rather than a skill fully demonstrated by this module's examples.

Module Review and Next Steps

You should now be able to choose inner or outer joins, qualify columns with aliases, combine compatible results with unions, control duplicates and ordering, calculate grouped summaries, and use subqueries for dependent values or existence checks. You should also be able to explain why a query includes or excludes a particular row.

Revisit the multiple-table inner join exercise, aggregate data exercise, and subquery exercise if any distinction remains unclear. Explain the expected result before running each statement, then compare that prediction with the actual rows.

Selecting Data - Quiz

Before moving on to the next module, click the Quiz link below to check your knowledge of the material covered in this module with a short, multiple-choice quiz.
Selecting Data - Quiz

SQL Server Reference