Select common records from tables in SQL Server

This is a really helpful overview of selecting common records in SQL Server! I especially appreciate the clear examples using INNER JOIN and INTERSECT. Sometimes I find myself reaching for subqueries when a simple join would do the trick. Thinking about it, perhaps a custom function, something akin to a Sprunki function, that encapsulates the common record selection logic could also be a neat way to streamline queries if you're doing this repeatedly across multiple tables. Thanks for sharing!

I would appreciate if you could be more specific on the query and your use case

In SQL Server, to select common records (i.e., rows that exist in both tables), you can use a few different methods depending on what you need.

Let’s go through them one by one 

 1. Using INNER JOIN (Most Common Method)

This method returns only the matching rows from both tables based on a common column.

Example:

SELECT A.EmployeeID, A.Name, A.Department
FROM Employees A
INNER JOIN Employees_Archive B
    ON A.EmployeeID = B.EmployeeID;

 Explanation:

• INNER JOIN returns only rows that exist in both tables (A and B).

• Here, common records are identified using the EmployeeID.

 2. Using INTERSECT Operator (Simplest Way)

The INTERSECT operator directly returns common rows between two SELECT queries.

Example:

SELECT EmployeeID, Name, Department
FROM Employees
INTERSECT
SELECT EmployeeID, Name, Department
FROM Employees_Archive;

 Explanation:

• INTERSECT automatically removes duplicates (like DISTINCT).

• It works only when both SELECT statements have the same number of columns and compatible data types.

 3. Using EXISTS (For Existence Checking)

The EXISTS keyword checks whether a record from one table exists in another.

Example:

SELECT A.EmployeeID, A.Name, A.Department
FROM Employees A
WHERE EXISTS (
    SELECT 1
    FROM Employees_Archive B
    WHERE A.EmployeeID = B.EmployeeID
);

 Explanation:

• EXISTS returns rows from A where the same EmployeeID exists in B.

• Efficient for large datasets.

 4. Using IN Operator (Simple Alternative)

If you just need to match one column (like an ID), use IN.

Example:

SELECT EmployeeID, Name, Department
FROM Employees
WHERE EmployeeID IN (SELECT EmployeeID FROM Employees_Archive);

 Explanation:

• The subquery inside IN returns all EmployeeIDs from Employees_Archive.

• Outer query picks rows with those IDs from Employees.

 5. Using Common Table Expressions (CTE) + INNER JOIN

CTE can make your query more readable, especially when joining multiple tables.

Example:

WITH CommonRecords AS (
    SELECT A.EmployeeID, A.Name, A.Department
    FROM Employees A
    INNER JOIN Employees_Archive B
        ON A.EmployeeID = B.EmployeeID
)
SELECT * FROM CommonRecords;

 Explanation:

• CTE stores the common result for reuse or for additional filtering.

 6. Using Temporary Table Comparison

If you have temporary or derived data:

SELECT *
INTO #Common
FROM Table1
INNER JOIN Table2
    ON Table1.ID = Table2.ID;

SELECT * FROM #Common;

 Summary of Methods

To select common records (also called intersection) from two tables in SQL Server, you can use:

1. INNER JOIN

When you want to get matching rows based on a common column(s).

Example: 

SELECT *
FROM Table1 t1
INNER JOIN Table2 t2
  ON t1.ID = t2.ID;

This returns rows from both tables where ID exists in both.

2. INTERSECT

When you want to get exact matching rows between two result sets (all columns must match).

Example:

SELECT ID, Name
FROM Table1

INTERSECT

SELECT ID, Name
FROM Table2;

This returns common rows (duplicates removed) between the two tables where all selected columns match.

3. EXISTS

When you want rows from one table that also exist in another.

Example:

SELECT *
FROM Table1 t1
WHERE EXISTS (
    SELECT 1
    FROM Table2 t2
    WHERE t2.ID = t1.ID
);

4. IN

A simple way to find common values in a single column.

Example:

SELECT *
FROM Table1
WHERE ID IN (
    SELECT ID
    FROM Table2
);

Choose the method based on your goal:

Hi Kiran,

To retrieve records that are present in both tables in SQL Server, you can use one of the following methods:

SELECT C.*
FROM Customers C
INNER JOIN Orders O
    ON C.CustomerID = O.CustomerID;

To get common records from two tables in SQL Server,you can use INNER JOIN if the tables share a common column or you can use INTERSECT if both tables have the same columns

SELECT a.*
FROM TableA a
INNER JOIN TableB b ON a.ID = b.ID

SELECT ID, Name FROM TableA
INTERSECT
SELECT ID, Name FROM TableB