SQL Tutorial - How to compare multiple columns in SQL
Learn how to compare multiple columns in SQL, in this video I cover comparing multiple columns in the WHERE clause, using CHECKSUM, BINARY_CHECKSUM and HASHBYTES. I also discuss the advantages and disadvantages of using CHECKSUM, BINARY_CHECKSUM and HASHBYTES and what is my preferred approach for comparing multiple columns.
An important point when using CHECKSUM, BINARY_CHECKSUM or HASHBYTES to compare multiple columns is that the column list show always be in the same order to get an accurate comparison. When using the HASHBYTES function, always add a separator that does not exist in the data such as a pipe.
If you would like more detail on the functions used in this video to compare multiple columns in SQL then take a look at the links below:
The SQL queries used in this video are available below:
DROP TABLE [dbo].[CustomerStaging];
CREATE TABLE [dbo].[CustomerStaging]
(
[SourceKey] [int] NULL,
[Title] [varchar](10) NULL,
[FirstName] [varchar](50) NULL,
[MiddleName] [varchar](50) NULL,
[LastName] [varchar](50) NULL,
[DOB] [date] NULL,
[AddressLine1] [varchar](50) NULL,
[AddressLine2] [varchar](50) NULL,
[City] [varchar](50) NULL,
[PostCode] [varchar](10) NULL
);
INSERT INTO (SourceKey, Title, FirstName, MiddleName, LastName, DOB, AddressLine1, AddressLine2, City, PostCode)
VALUES
(1000, ’Mrs’, ’Deborah’, NULL, ’Wilkinson’, ’19960513’, ’1 The Barns’, NULL, ’London’, ’NE1 1RS’),
(2000, ’Dr’, ’Siobhan’, ’Mary’, ’Jones’, ’19840822’, ’Portobello House’, ’Yarn Street’, ’Birmingham’, ’B16 1PZ’),
(3000, ’Mr’, ’Karl’, NULL, ’Smith’, ’19790906’, ’5 Main Street’, NULL, ’Manchester’, ’M1 1PQ’);
DROP TABLE [dbo].[Customer];
CREATE TABLE [dbo].[Customer]
(
[CustomerKey] [int] NOT NULL IDENTITY(1, 1),
[SourceKey] [int] NULL,
[Title] [varchar](10) NULL,
[FirstName] [varchar](50) NULL,
[MiddleName] [varchar](50) NULL,
[LastName] [varchar](50) NULL,
[DOB] [date] NULL,
[AddressLine1] [varchar](50) NULL,
[AddressLine2] [varchar](50) NULL,
[City] [varchar](50) NULL,
[PostCode] [varchar](10) NULL
);
INSERT INTO (SourceKey, Title, FirstName, MiddleName, LastName, DOB, AddressLine1, AddressLine2, City, PostCode)
VALUES
(1000, ’Miss’, ’Deborah’, NULL, ’Austin’, ’19960513’, ’1 The Barns’, NULL, ’London’, ’NE1 1RS’),
(2000, ’Dr’, ’Siobhan’, ’Mary’, ’Jones’, ’19840822’, ’Portobello House’, ’Yarn Street’, ’Birmingham’, ’B16 1PZ’),
(3000, ’Mr’, ’Karl’, NULL, ’Smith’, ’19790906’, ’250 Wood Road’, NULL, ’Manchester’, ’M25 1HH’);
-- CHECKSUM
SELECT
*
FROM AS A
LEFT OUTER JOIN AS B
ON =
WHERE
CHECKSUM(
,
,
,
,
,
,
,
,
)
[replace with not equal to]
CHECKSUM(
,
,
,
,
,
,
,
,
)
-- BINARY_CHECKSUM
SELECT
*
FROM AS A
LEFT OUTER JOIN AS B
ON =
WHERE
BINARY_CHECKSUM(
,
,
,
,
,
,
,
,
)
[replace with not equal to]
BINARY_CHECKSUM(
,
,
,
,
,
,
,
,
)
-- HASHBYTES
SELECT
*
FROM AS A
LEFT OUTER JOIN AS B
ON =
WHERE
HASHBYTES(’SHA2_512’,
CONCAT(
,
,
,
,
,
,
,
,
)
)
[replace with not equal to]
HASHBYTES(’SHA2_512’,
CONCAT(
,
,
,
,
,
,
,
,
)
)
4 views
788
228
6 months ago 01:28:06 1
#002 Django СТРИМ 🔴 Рабочее окружение Backend разработчика + необходимые знания
6 months ago 02:37:26 1
Linux сервер для Django проекта (Nginx, gunicorn, PostgreSQL) + простой CI/CD 🔴 #008 Django СТРИМ
6 months ago 04:19:34 40
Learn PostgreSQL Tutorial - Full Course for Beginners
6 months ago 04:52:51 1
Artificial Intelligence Full Course | Artificial Intelligence Tutorial for Beginners | Edureka
6 months ago 00:59:12 24
face-to-many comfyui pinokio (PS2 Graphics) tutorial + more
6 months ago 03:10:25 1
СУБД для начинающих - Windows-приложение на C# c базой данных в MySQL - туториал на русском
6 months ago 00:09:34 1
C# MongoDB Beginner’s Guide: How To Update Documents