How to Compare Tables in SQL Server?

Updated: Jan 26, 2020

Sometimes you need to compare data between two tables with many rows and columns. There are several ways to achieve this. Here is one of them...




One of our customers has recently asked me the following question (I changed just the business case):


The Problem


“I have a “MemberSessions” table, which stores web sessions made by members. The table contains the “MemberId” column, as well as other columns that describe the session, such as “LoginDateTime”. I would like to find all members with the exact same sessions.”


Let’s create the table and populate it with some test data:

CREATE SCHEMA
	Operation;
GO


CREATE TABLE
	Operation.MemberSessions
(
	Id				INT	IDENTITY (1,1)	NOT NULL ,
	MemberId		INT					NOT NULL ,
	LoginDateTime	DATETIME2(0)		    NOT NULL ,
	EndDateTime		DATETIME2(0)		NULL ,
	EndReasonId		TINYINT	                NULL ,


	CONSTRAINT
		pk_MemberSessions_c_Id
	PRIMARY KEY CLUSTERED 
		(Id ASC)
);
GO


INSERT INTO
	Operation.MemberSessions
(
	MemberId ,
	LoginDateTime ,
	EndDateTime ,
	EndReasonId
)
VALUES
	(1	, '2015-12-03 13:01:10'	, '2015-12-03 15:50:53'	, 1) ,
	(1	, '2015-12-08 12:47:53'	, '2015-12-08 13:11:43'	, 1) ,
	(2	, '2015-12-03 13:01:10'	, '2015-12-03 15:50:53'	, 1) ,
	(2	, '2015-12-08 12:47:53'	, '2015-12-08 13:11:43'	, 1) ,
	(3	, '2015-12-14 09:45:01'	, '2015-12-14 09:48:52'	, 1) ,
	(3	, '2015-12-15 10:33:41'	, '2015-12-15 11:02:17'	, 2) ,
	(4	, '2012-08-09 11:17:05'	, '2012-08-09 15:02:07'	, 1) ,
	(5	, '2012-11-23 19:51:44'	, '2012-11-23 20:15:16'	, 1) ,
	(5	, '2014-09-28 04:10:26'	, '2014-09-28 06:00:37'	, 3) ,
	(5	, '2006-10-29 17:10:47'	, '2006-10-29 20:33:40'	, 2);
GO

In this example, there are 5 members. Notice that members #1 and #2 have the exact same sessions with all data exactly the same (LoginDateTime, EndDateTime and EndReasonId). All other members have different sessions.


So the customer wanted to get the following result:

Where “MemberGroup” is an incremental number that identifies different groups of similar members. If there was another group of similar members (in terms of session data), then they would have the same member group value (2), and so on…


The Solution


There are several ways to try