Total Page Preview:   000000001453

How to find not exists or not match rows between two table with with example in sql server

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:

1. Using Not Exists Method:
 
------------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
)
 
 
2. Using Left Outer Join:
 
------------Find not match rows/records between two table------------------
SELECT
t2.ProductId
,t2.ProductName
,t2.ProductPrice
,t2.Quintity 
 
FROM [dbo].[ProductTable2] AS t2
LEFT OUTER JOIN [dbo].[ProductTable1] AS t1 
ON t1.ProductId = t2.ProductId AND t1.ProductName = t2.ProductName AND t1.ProductPrice= t2.ProductPrice
AND  t1.Quintity = t2.Quintity
WHERE t1.ProductId IS NULL AND t1.ProductName IS NULL AND t1.ProductPrice IS NULL AND t1.Quintity IS NULL
 

 

 
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)
 
 
 
Thanks!!!
Brijesh Kumar
 

Thank You

About Author

Brijesh Kumar

Database Developer

I have more then 6 years Experience in Microsoft Technologies - SQL Server Database, ETL Azure Cloud - Azure SQL Database, CosmosDB, Azure Data Factory, PowerBI, Web Job, Azure Function, Azure Storage, Web Apps, Powershall and Database Migration On-Premise to Azure Cloud.
LinkedIn : https://www.linkedin.com



Comments

Sandeep
16-Feb-2017
What if a record which is not in table2 but it present in table1 , i think in that case left outer join won't work ..it will give the records which is present in table2 and not similar to table1 record

                           
                           

                           

                           

Facebook User: