How to Retrieve non matching records from database in SQL Server


In this article i'm going to tell you how to retrieve non matching records from sql server table. To retrieve non matching record i'm using Left Join in select query

For better understanding purpose i created a image which give more clarity to understand Output Table is the result of non matching records from 2 tables as i shown in below image

How to Retrieve non matching records from database in SQL Server

In above image we have 2 table i.e Technology & DuplicateTechnology from left table Technology i'm getting non matching records from right table DuplicateTechnology .

First let me create Table's, Follow the query to create Table and insert records

Create Technology Table in SQL

I'm creating this tables in master Database

CREATE TABLE [dbo].[Technology](
 [techid] [int] NULL,
 [techname] [varchar](30) NULL
) ON [PRIMARY]

GO

INSERT [dbo].[Technology] ([techid], [techname]) VALUES (1, N'Asp.Net')
INSERT [dbo].[Technology] ([techid], [techname]) VALUES (2, N'C#.Net')
INSERT [dbo].[Technology] ([techid], [techname]) VALUES (3, N'SQL Server')
INSERT [dbo].[Technology] ([techid], [techname]) VALUES (4, N'Java')
INSERT [dbo].[Technology] ([techid], [techname]) VALUES (5, N'JQuery')
INSERT [dbo].[Technology] ([techid], [techname]) VALUES (6, N'AngularJS')
INSERT [dbo].[Technology] ([techid], [techname]) VALUES (7, N'Sharepoint')
INSERT [dbo].[Technology] ([techid], [techname]) VALUES (8, N'Android')
INSERT [dbo].[Technology] ([techid], [techname]) VALUES (9, N'Linux')
INSERT [dbo].[Technology] ([techid], [techname]) VALUES (10, N'PHP')

Create DuplicateTechnology Table in SQL

I'm creating this tables in master Database

 Create Table DuplicateTechnology
(
 dtid int,
 dtname varchar(30)
)

insert into DuplicateTechnology values(1, 'SQL Server')
insert into DuplicateTechnology values(2, 'JQuery')
insert into DuplicateTechnology values(3, 'Sharepoint')
insert into DuplicateTechnology values(4, 'Android')
insert into DuplicateTechnology values(5, 'PHP')

Inner Join :

Inner Join is used to retrive matching records from both the tables.

 select * from Technology t inner join DuplicateTechnology dt on t.techname=dt.dtname

Left Join

  • It is used to retrieve all the records from left table and matching records from right table.
  • If they is no matching records in right table then display NULL value.
 select t.* from Technology t left outer join DuplicateTechnology dt on t.techname=dt.dtname where dt.dtname is Null
Tag : sql server
0 Komentar untuk "How to Retrieve non matching records from database in SQL Server"

Back To Top