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
1043
309
3 months ago 00:08:30 2
Как собрать Зеркальный Скваер-0 | | How to Solve the Mirror Square-0 | SQ-0 Tutorial
3 months ago 00:14:06 2
How to Connect to PostgreSQL with EF Core: A Step-by-Step Guide
3 months ago 00:08:28 10
Лучшие бесплатные курсы и книги по Python в 2024 год.
4 months ago 02:49:35 1
Inventory Management System(C# Full Project using SQL server with source code)
4 months ago 00:09:42 1
SQL Tutorial #13 - SQL EXISTS and NOT EXISTS Operator
4 months ago 01:01:21 1
Data Analyst Portfolio Project - SQL | Step-by-Step Guide From SQL Database to Interactive Dashboard
4 months ago 00:40:50 1
Full Project in Excel | Excel Tutorials for Beginners
4 months ago 00:19:07 1
Как создать базу данных MSSQL Server [Базы данных для C# программиста] #1
5 months ago 01:28:06 2
#002 Django СТРИМ 🔴 Рабочее окружение Backend разработчика + необходимые знания
5 months ago 02:37:26 1
Linux сервер для Django проекта (Nginx, gunicorn, PostgreSQL) + простой CI/CD 🔴 #008 Django СТРИМ
5 months ago 04:19:34 48
Learn PostgreSQL Tutorial - Full Course for Beginners
5 months ago 04:52:51 1
Artificial Intelligence Full Course | Artificial Intelligence Tutorial for Beginners | Edureka
5 months ago 00:59:12 40
face-to-many comfyui pinokio (PS2 Graphics) tutorial + more
5 months ago 03:10:25 1
СУБД для начинающих - Windows-приложение на C# c базой данных в MySQL - туториал на русском
5 months ago 00:09:34 1
C# MongoDB Beginner’s Guide: How To Update Documents
5 months ago 00:27:23 2
FastAPI - Базы данных и миграции Alembic #4
5 months ago 00:15:58 1
A Beginner’s Guide to MongoDB Filtering in C#
5 months ago 00:21:20 1
ВСЁ про Базы Данных в одном месте / ХАЙЛАЙТЫ
5 months ago 01:07:50 1
Учим Базы Данных за 1 час! #От Профессионала
6 months ago 15:18:23 1
IBM Data Analyst Complete Course | Data Analyst Tutorial For Beginners,
7 months ago 00:29:47 1
How I would learn & master it if I were to start over again
7 months ago 00:05:30 1
SQL. Урок 1.2 - Создание пользователя через PgAdmin