Get Distinct Unique fields from Multiple SQL tables

Today in this article, we will see how to get distinct unique fields from Multiple SQL tables.

Updated October 2026: sections on counting, filtering and UNION pitfalls, plus an FAQ.

We will write a query to get a unique employee id from a table and then from a collection of tables.

We will cover below scenarios,

Get distinct unique fields from a Single table

Let’s get distinct unique fields from a single SQL table later we will extend it for multiple SQL tables

Command

select distinct <fieldname> from  <table> with (nolock) 

Example

select distinct employeeId from 
Employee with (nolock)

Get distinct unique fields from Multiple SQL tables

Let’s now get distinct unique fields from multiple SQL tables

Command

 select distinct <fieldname> from  <table1> with (nolock)  
 union
 select distinct <fieldname> from  <table2> with (nolock)  
 union
 select distinct <fieldname> from  <table3> with (nolock)  
 union
 select distinct <fieldname> from  <table4> with (nolock)  
 

Example

select distinct employeeId from Employee with (nolock)

union

select distinct  employeeId from FinanceRecord with (nolock)

union

select distinct  employeeId  from PersonalRecord with (nolock) 

Please note that UNION concatenates result sets from two queries and does not create individual rows from columns gathered from two tables and also helps excludes duplicates.

UNION errors, NULLs and when to use a JOIN instead

A UNION stacks rows from several queries into one list, while a JOIN widens rows by placing columns from two tables side by side. If you want one list of unique employee IDs, use UNION. A JOIN gives you matched pairs, not a single combined column.

What rules must every SELECT in a UNION follow?

Each SELECT must return the same number of columns, in the same order, with data types that can be implicitly converted. Break the first rule and SQL Server stops with an error saying all queries combined with UNION, INTERSECT or EXCEPT must have an equal number of expressions in their target lists.

• Column count: add the missing column to the shorter query, or put a NULL placeholder in the matching position.
• Data types: if one table stores the ID as int and another as varchar, SQL Server uses data type precedence to pick the result type. That can turn into a conversion failure on the first non-numeric value. Wrap the column in an explicit CAST to the type you want, such as varchar(20), in every branch.
• Column names: the result takes its names from the first SELECT. Aliases in later branches don’t matter, and an ORDER BY can only use the first query’s names.

How does UNION treat NULLs and DISTINCT?

UNION treats NULLs as equal when it removes duplicates, so you get one NULL row however many branches contain one. UNION ALL keeps every NULL.

Because UNION already removes duplicates, a DISTINCT inside each SELECT is logically redundant. It can still help on large tables, because each side shrinks before the merge. It can also add a pointless extra sort. Test both versions against your own data and compare the execution plans.

To combine three or more tables, chain more UNIONs. Just keep the column rules identical in every branch.

When do INTERSECT and EXCEPT fit better?

Use them when you’re asking a different question than “everything from all tables”. They follow the same column rules as UNION and also remove duplicates.

For example, EXCEPT can find employee IDs that exist in Employee but have no row in FinanceRecord. A plain distinct list can’t tell you that, and neither can per-table counts, since an ID in both tables would have two different counts.

Get distinct unique fields with different field names

It could be possible that fields or column names from a single SQL table are not matching with other SQL tables but values are matching.

In such case later we will extend the query as below to match the multiple SQL tables

Command

select distinct <fieldname> from  <table1> with (nolock)  
union
select distinct <newfield1> as <fieldname> from  <table2> with (nolock)  
union
select distinct <newfield2> as <fieldname> from  <table2> with (nolock) 
union
select distinct <fieldname> from  <table4> with (nolock)  

Example

select distinct employeeId from 
Employee with (nolock) where updatedDate > (getDate())

union

select distinct  employeeId 
from FinanceRecord with (nolock) where updatedDate > (getDate()) 

union 

select distinct empUnique as employeeId  
from PersonalRecord with (nolock) where updatedDate > (getDate())

References :

• Configure DbContext in Entity framework ASP.NET Core – Best Practices

Do you have any comments or ideas or any better suggestions to share?

Please sound off your comments below.

Happy Coding !!

Please bookmark this page and share it with your friends. Please Subscribe to the blog to receive notifications on freshly published (2026) best practices and guidelines for software design and development.

Frequently asked questions

Do I need DISTINCT inside each SELECT when I use UNION?

No. UNION already removes duplicates across the whole combined result, so the distinct in each branch is logically redundant. It can occasionally help on large tables, because it shrinks what each side contributes before the merge. Measure both versions on your own data before you keep it.

What is the difference between UNION and UNION ALL?

UNION removes duplicate rows, and it needs a sort or hash step to do that. UNION ALL keeps every row and skips deduplication, so it’s usually faster. Use UNION ALL only when the inputs can’t overlap, or when you aggregate the result afterwards.

How do I count distinct values across multiple tables?

Put the UNION in a subquery or CTE and run COUNT(*) on it. Counting each table separately and adding the numbers double-counts any value that appears in more than one table.

SQL

select count(*) as UniqueEmployees
from (
    select employeeId from Employee
    union
    select employeeId from FinanceRecord
    union
    select empUnique from PersonalRecord
) as allIds;

Why do I get an error that each UNION query must have the same number of columns?

Every SELECT in a UNION must return the same number of columns, in the same order, with types that can be implicitly converted. Fix it by matching the column lists, adding NULL or literal placeholders where a table lacks a column, and using CAST where the types differ.

SQL

select employeeId, cast(salary as decimal(12,2)) as amount
from FinanceRecord
union
select empUnique, null
from PersonalRecord;

Share on Facebook

Post on X

Follow us

Save