In this article i am going to explain How to find not exists or not match rows between two table with with example in sql server
Sometimes you may want to compare two tables and identify records in one of the tables that have no corresponding records in the other table.So I am expainig below:
------------Find not match rows/records between two table------------------
SELECT t1.ProductId, t1.ProductName, t1.ProductPrice, t1.Quintity FROM [dbo].[ProductTable2] AS t1 WHERE
NOT EXISTS(SELECT t2.ProductId, t2.ProductName, t2.ProductPrice, t2.Quintity FROM [dbo].[ProductTable1] AS t2
WHERE t1.ProductId = t2.ProductId AND t1.ProductName = t2.ProductName AND t1.ProductPrice= t2.ProductPrice
AND t1.Quintity = t2.Quintity
)
Get SQL Scripts for Create Tables and Insert Data:
---------------Create table 1------------
CREATE TABLE [dbo].[ProductTable1](
[ProductId] [int] NULL,
[ProductName] [varchar](50) NULL,
[ProductPrice] [float] NULL,
[Quintity] [int] NULL
) ON [PRIMARY]
---------------Create table 2------------
CREATE TABLE [dbo].[ProductTable2](
[ProductId] [int] NULL,
[ProductName] [varchar](50) NULL,
[ProductPrice] [float] NULL,
[Quintity] [int] NULL
) ON [PRIMARY]
GO
--------------------------------- Insert record into ProductTable1------------------------
INSERT [dbo].[ProductTable1] ([ProductId], [ProductName], [ProductPrice], [Quintity]) VALUES (1, N'Mobile', 5200, 15)
INSERT [dbo].[ProductTable1] ([ProductId], [ProductName], [ProductPrice], [Quintity]) VALUES (2, N'Laptop', 25452, 25)
INSERT [dbo].[ProductTable1] ([ProductId], [ProductName], [ProductPrice], [Quintity]) VALUES (3, N'Desktop', 18451, 8)
--------------------------------- Insert record into ProductTable2------------------------
INSERT [dbo].[ProductTable1] ([ProductId], [ProductName], [ProductPrice], [Quintity]) VALUES (1, N'Mobile', 5200, 15)
INSERT [dbo].[ProductTable1] ([ProductId], [ProductName], [ProductPrice], [Quintity]) VALUES (2, N'Laptop', 25452, 25)
INSERT [dbo].[ProductTable1] ([ProductId], [ProductName], [ProductPrice], [Quintity]) VALUES (3, N'Desktop', 18451, 8)
INSERT [dbo].[ProductTable2] ([ProductId], [ProductName], [ProductPrice], [Quintity]) VALUES (4, N'HeadPhone', 2500, 50)
INSERT [dbo].[ProductTable2] ([ProductId], [ProductName], [ProductPrice], [Quintity]) VALUES (5, N'Tablet', 4500, 15)