Select common records from tables in SQL Server

Question

How to select common records from tables in SQL Server

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

Answers (3)