Select common records from tables in SQL Server

Question

How to select common records from tables in SQL Server

Aaron Blake · Answer

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!

Prasad Raveendran · Answer

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

Sandhiya Priya · Answer

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 EmployeeID s 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 Method Description Best For INNER JOIN Returns matching rows based on condition Most commonly used INTERSECT Returns exact matching rows from both tables Same structure tables EXISTS Checks existence of a row in another table Large datasets IN Checks if a column value exists in another set Simple single-column check CTE + JOIN Structured and reusable approach Complex queries

Ajay Bansode · Answer

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: Scenario Use Common rows based on a key INNER JOIN, IN, EXISTS Exact same rows in both tables INTERSECT Better performance with large data EXISTS or JOIN

Jignesh Kumar · Answer

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;

Cynthia Sathuragiri · Answer

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

Select common records from tables in SQL Server

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (6)