Combine Customer Records with T-SQL

Step 1: Combine Related Data with INNER JOIN

When working with related tables, you often need to bring information together to answer real-world questions. An INNER JOIN returns only the rows where a relationship exists in both tables.

In this step, you’ll connect to a preloaded SQL Server database called TransactionsDB, which contains tables named Customers and Transactions. The Customers table stores customer details (such as first and last names), and the Transactions table stores purchase records (such as dates, amounts, and payment methods).
You’ll begin by writing T-SQL queries to:

• Return only customers who have made at least one purchase by joining Customers to Transactions.
• Create a readable CustomerName using CONCAT to combine first and last names.
• Use real-world table aliases (c and t) to write concise, professional SQL.

What You’ll Learn in This Step

• Use INNER JOIN to combine related rows from Customers and Transactions.
• Build a CustomerName using the CONCAT function.
• Apply realistic table aliases (c, t) to improve readability.

Note: The SQL Server environment has already been set up for you. Your focus will be on writing and executing queries; no installation or configuration is required.

info> You will write all of your T-SQL answers directly in the 1-step-one.sql file provided in this environment.

When you are ready to run the query, simply press Run in the terminal.
This will automatically execute the contents of 1-step-one.sql against the database and display the results.
You do NOT need to type any commands in the terminal manually.

sqlcmd -S localhost -U SA -P 'P@ssword1'

### Combine Two Tables Using INNER JOIN

The INNER JOIN operator links rows from two tables based on a matching condition. It only returns rows where the values in the linking columns match in both tables.

Key Concepts

• The ON clause defines the relationship between the tables. Example: Customers.CustomerID = Transactions.CustomerID
• When a customer has no matching transaction, their row will not appear in the results.
• Use fully qualified column names (table.column) when two tables share similar column names.

Pattern Example

SELECT table1.column_a, table2.column_b
FROM table1
INNER JOIN table2
    ON table1.shared_column = table2.shared_column;
GO

Applying to This Dataset

• Customers.CustomerID uniquely identifies each customer (primary key).
• Transactions.CustomerID references the customer who made each transaction (foreign key).
• Joining these tables will show only customers who have at least one transaction.

Use Table Aliases for Cleaner Queries

Table aliases are short labels that make SQL queries easier to read and write, especially when working with multiple tables.

Key Concepts

• Define an alias with the AS keyword: Example: Customers AS c
• Prefix every column with its alias to avoid ambiguity.
• Aliases do not change the results, they only simplify the query.

Pattern Example

SELECT a.column_x, b.column_y
FROM table_a AS a
INNER JOIN table_b AS b
    ON a.key_column = b.key_column;
GO

Applying to This Dataset

• Use c as the alias for Customers.
• Use t as the alias for Transactions.
• This keeps the query realistic and professional, while maintaining clarity.

Step 2: Include All Customers, Even Those Without Transactions

In many cases, you need to see a complete list of customers, even those who have not yet made a purchase. An INNER JOIN excludes rows that don’t have matches in both tables. To include unmatched rows from one table, you use a LEFT JOIN.

In this step, you’ll work with the Customers and Transactions tables to:

• Show every customer in the database, along with their transaction details when available.
• Identify which customers have never completed a transaction.

By the end of this step, you will be able to write queries that keep unmatched rows in the results, helping you find gaps such as inactive or new customers.

What You’ll Learn in This Step

• Use LEFT JOIN to include all rows from one table, even without matching rows in the other table.
• Filter for rows that have no matches using NULL values.
• Apply table aliases to keep your queries clean and professional.

info> You will write all of your T-SQL answers directly in the 2-step-two.sql file provided in this environment.

When you are ready to run the query, simply press Run in the terminal. This will automatically execute the contents of 2-step-two.sql against the database and display the results. You do NOT need to type any commands in the terminal manually.

--- ### Show All Customers with LEFT JOIN

A LEFT JOIN returns all rows from the first table listed in the query, even if there are no matching rows in the second table. When there is no match, the columns from the second table will contain NULL values.

Key Concepts

• The LEFT JOIN is useful for identifying missing relationships, such as customers without any transactions.
• The first table in the query is the "anchor." All of its rows will always appear in the results.
• Columns from the second table will only be populated if a match is found.

Pattern Example

SELECT table1.column_a, table2.column_b
FROM table1
LEFT JOIN table2
    ON table1.shared_column = table2.shared_column;
GO

Applying to This Dataset

• Use Customers as the first table to guarantee every customer appears in the results.
• Match rows using Customers.CustomerID = Transactions.CustomerID.
• When a customer has no transactions, all Transactions columns will show NULL.

Identify Customers Without Any Transactions

When using a LEFT JOIN, unmatched rows from the second table will contain NULL values. By checking for these NULL values, you can find rows that exist only in the first table.

Key Concepts

• The IS NULL condition is used to filter for unmatched rows.
• This is a common way to find missing data, such as customers who haven’t purchased anything.

Pattern Example

SELECT table1.column_a
FROM table1
LEFT JOIN table2
    ON table1.shared_column = table2.shared_column
WHERE table2.shared_column IS NULL;
GO

Applying to This Dataset

• The Transactions.TransactionID column will be NULL for customers with no transactions.
• Filter the results using WHERE t.TransactionID IS NULL to show only those customers.

Step 3: Filter Joined Data Correctly

When you apply filters in a query with joins, the placement of the filter matters. Using a WHERE clause with certain join types can unintentionally change which rows are returned. This is especially important with LEFT JOIN, where you want to keep unmatched rows.

In this step, you’ll:

• See what happens when a filter is applied in the WHERE clause of a LEFT JOIN.
• Correctly apply the filter inside the ON clause to keep all rows from the first table.
• Write a query that combines three tables together.

By the end of this step, you will know how to write accurate, predictable joins when filtering.

What You’ll Learn in This Step

• How filter placement affects results when using a LEFT JOIN.
• The difference between applying a condition in the ON clause vs. the WHERE clause.
• How to join three tables in a single query.

info> You will write all of your T-SQL answers directly in the 3-step-three.sql file provided in this environment.

When you are ready to run the query, simply press Run in the terminal. This will automatically execute the contents of 3-step-three.sql against the database and display the results. You do NOT need to type any commands in the terminal manually.

--- ### Filtering with WHERE in a LEFT JOIN

A LEFT JOIN is meant to include every row from the first table, even if there are no matches in the second table. However, when you place a condition in the WHERE clause on a column from the second table, it removes unmatched rows and behaves like an INNER JOIN.

Key Concepts

• LEFT JOIN without filtering preserves unmatched rows, showing NULL for columns from the second table.
• Adding a filter in the WHERE clause on the second table forces those unmatched rows to be excluded.

Generic Example

SELECT a.column_x, b.column_y
FROM table_a AS a
LEFT JOIN table_b AS b
    ON a.id = b.id
WHERE b.status = 'Active';
GO

Result:

• Only rows where b.status = 'Active' are included.
• Rows with no match in table_b are filtered out, just like an INNER JOIN.

Applying to This Dataset

• Filtering Transactions on a column like PaymentMethod in the WHERE clause will exclude customers who have no matching transactions.

Filtering Correctly with the ON Clause

To keep all rows from the first table while still applying a filter, move the condition into the ON clause instead of the WHERE clause.

Key Concepts

• A filter in the ON clause limits which rows from the second table are matched.
• Unmatched rows are still included, with NULL values for columns from the second table.

Example

SELECT a.column_x, b.column_y
FROM table_a AS a
LEFT JOIN table_b AS b
    ON a.id = b.id AND b.status = 'Active';
GO

Result:

• All rows from table_a appear in the results.
• Only rows from table_b with status = 'Active' are matched.

Applying to This Dataset

• Adding AND t.PaymentMethod = 'Credit Card' inside the join condition keeps all customers while showing only credit card transactions when they exist.

Joining Three Tables

When working with multiple relationships, you can join more than two tables in a single query. Each join is added one at a time, creating a chain of relationships.

Key Concepts

• The join order determines how rows are matched step-by-step.
• Each additional table must have its own ON clause that defines how it connects to the existing tables.
• Use clear aliases to keep the query readable.

Generic Example

SELECT a.column_x, b.column_y, c.column_z
FROM table_a AS a
INNER JOIN table_b AS b
    ON a.id = b.id
INNER JOIN table_c AS c
    ON b.id = c.id;
GO

Applying to This Dataset

• Join Customers to Transactions on CustomerID.
• Join Transactions to TransactionDetails on TransactionID.

Step 4: Combine Rows from Multiple Tables

Sometimes related data is stored in multiple tables with the same structure, such as separate tables for different regions or years. To analyze the data together, you need to combine the rows into a single result set.

The UNION and UNION ALL operators are used to combine results from multiple SELECT statements into a single query.

In this step, you’ll:

• Combine customer data stored in two regional tables into one result set.
• Compare the behavior of UNION versus UNION ALL.

By the end of this step, you will know how to merge multiple datasets and decide which operator is appropriate for your scenario.

What You’ll Learn in This Step

• Use UNION to combine results and automatically remove duplicate rows.
• Use UNION ALL to keep duplicate rows when combining data.
• Ensure that each SELECT in a union has the same number of columns and compatible data types.

info> You will write all of your T-SQL answers directly in the 4-step-four.sql file provided in this environment.

When you are ready to run the query, simply press Run in the terminal. This will automatically execute the contents of 4-step-four.sql against the database and display the results. You do NOT need to type any commands in the terminal manually.

--- ### Combining Rows with UNION

The UNION operator merges the results of two or more SELECT statements into a single result set. Duplicate rows are automatically removed, showing only unique records.

Key Concepts

• Each SELECT must return the same number of columns with compatible data types.
• The column names in the final result are taken from the first SELECT statement.
• UNION performs a distinct operation, removing duplicates between the combined datasets.

Example

SELECT column_a, column_b
FROM table1
UNION
SELECT column_a, column_b
FROM table2;
GO

Applying to This Dataset

• The Customers_NA and Customers_EU tables contain identical columns for different regions.
• A UNION query can combine both into a single list of unique customers.

Keeping Duplicates with UNION ALL

The UNION ALL operator works like UNION, but it does not remove duplicate rows. This makes it faster because the database doesn’t have to check for duplicates, and it is useful when you want to see every record exactly as it exists.

Key Concepts

• UNION ALL includes every row from both datasets, even if they are identical.
• It is best used when you are confident that duplicates are acceptable or when you need to track exact counts.

Example

SELECT column_a, column_b
FROM table1
UNION ALL
SELECT column_a, column_b
FROM table2;
GO

Applying to This Dataset

• Using UNION ALL on the Customers_NA and Customers_EU tables will return every row, even if the same customer exists in both regions.

Step 5: Ensure Compatibility and Order in UNION Results

When combining datasets using UNION or UNION ALL, the structure and data types of each SELECT must be compatible. If there are differences between the queries, errors will occur. In addition, the final combined result set can be sorted to make it easier to read and analyze.

In this step, you’ll:

• Add sorting to a combined query for a clean, final report.
• Fix mismatched column counts by adding placeholders.
• Resolve data type mismatches using the CAST function.
• Write a complex query that combines joined data from two years.

By the end of this step, you will be able to create professional, production-ready UNION queries that return clean, compatible, and properly ordered results.

What You’ll Learn in This Step

• Apply ORDER BY to sort a complete unioned result set.
• Use NULL placeholders to fix mismatched column counts.
• Resolve incompatible data types using CAST.
• Combine data from multiple joined tables into a single result.

info> You will write all of your T-SQL answers directly in the 5-step-five.sql file provided in this environment.

When you are ready to run the query, simply press Run in the terminal. This will automatically execute the contents of 5-step-five.sql against the database and display the results. You do NOT need to type any commands in the terminal manually.

--- ### Sorting UNION Results with ORDER BY

When you combine results with UNION or UNION ALL, the ORDER BY clause must be placed after the entire unioned query, not inside the individual SELECT statements.

Key Concepts

• ORDER BY applies to the final combined dataset.
• You can sort by one or more columns, separated by commas.
• Sorting can be ascending (ASC) or descending (DESC).

Example

SELECT column_a, column_b
FROM table1
UNION ALL
SELECT column_a, column_b
FROM table2
ORDER BY column_a, column_b;
GO

Applying to This Dataset

• Combine rows from the Customers_NA and Customers_EU tables.
• Sort the final result set by Country, then by CustomerName.

Fixing Mismatched Column Counts with NULL

When using UNION or UNION ALL, each SELECT must return the same number of columns in the same order. If one table has an extra column, you can add a NULL placeholder to the other SELECT to make them compatible.

Key Concepts

• The number of columns must match exactly.
• Use NULL with an alias as a placeholder for missing columns.

Example

SELECT column_a, column_b, NULL AS placeholder_column
FROM table1
UNION ALL
SELECT column_a, column_b, column_c
FROM table2;
GO

Applying to This Dataset

• If one region has a Phone column but the other does not, include a NULL AS Phone in the query that lacks it.

Fixing Data Type Mismatches with CAST

Both SELECT statements in a UNION must return compatible data types for each column position. When columns have incompatible data types, use the CAST function to convert them to a shared type.

Key Concepts

• CAST(expression AS data_type) converts a value to the specified type.
• Both sides of the union must use the same data type for each column.

Example

SELECT CAST(column_a AS NVARCHAR(20)) AS column_a
FROM table1
UNION ALL
SELECT column_a
FROM table2;
GO

Applying to This Dataset

• If one table stores amounts as DECIMAL and another as NVARCHAR, use CAST to make both columns compatible.

Combining Joins and UNION ALL

You can combine multiple joins and then merge the results using UNION ALL. Each side of the union must be a complete query with the same structure.

Key Concepts

• Perform joins first, then use UNION ALL to merge the results.
• Each joined query must have matching column order and data types.

Example

SELECT a.column_x, b.column_y
FROM table_a AS a
INNER JOIN table_b AS b
    ON a.id = b.id
UNION ALL
SELECT a.column_x, b.column_y
FROM table_c AS a
INNER JOIN table_d AS b
    ON a.id = b.id;
GO

Applying to This Dataset

• Join Sales_2024 with Customers for the first query.
• Join Sales_2025 with Customers for the second query.
• Combine both joined datasets with UNION ALL.