How to Retrieve non matching records from database in SQL Server

Retrieving Non-Matching Records in SQL Server Using Left Join

In this article, we'll explore the process of retrieving non-matching records from a SQL Server table using the Left Join in a SELECT query. The methodology involves comparing two tables and extracting records that do not have corresponding matches in the other table. To facilitate a better understanding, an illustrative image is provided, demonstrating the output table resulting from non-matching records.

In the image above, we have two tables, namely Technology and DuplicateTechnology. The left table, Technology, contains non-matching records retrieved from the right table, DuplicateTechnology.

Table Creation and Record Insertion

Let's start by creating and populating the tables. The Technology table is created in the master database.

Create Technology Table in SQL

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

INSERT [dbo].[Technology] ([techid], [techname]) VALUES (1, N'Asp.Net')
-- Additional insert statements for other records

Create DuplicateTechnology Table in SQL

CREATE TABLE DuplicateTechnology
(
 dtid int,
 dtname varchar(30)
)

INSERT INTO DuplicateTechnology VALUES(1, 'SQL Server')
-- Additional insert statements for other records

Retrieving Non-Matching Records using Left Join

Now, let's dive into the SQL queries for extracting non-matching records. The Left Join is a crucial component in this process.

Inner Join

SELECT * FROM Technology t INNER JOIN DuplicateTechnology dt ON t.techname=dt.dtname

Left Join

• It retrieves all records from the left table (Technology) and matching records from the right table (DuplicateTechnology).
• If there are no matching records in the right table, it displays NULL values.

SELECT t.* FROM Technology t LEFT OUTER JOIN DuplicateTechnology dt ON t.techname=dt.dtname WHERE dt.dtname IS NULL

Explanation of the Markup Code

The HTML markup code in this post serves as a visual aid for readers to understand the concept of retrieving non-matching records. The image provides a clear representation of the output table, emphasizing how records from the left table (Technology) that do not have corresponding matches in the right table (DuplicateTechnology) are selected.

The SQL queries are explained step by step, guiding readers through the process of table creation, record insertion, and the utilization of Inner Join and Left Join to extract matching and non-matching records, respectively.

By following this comprehensive guide, users can gain a practical understanding of leveraging SQL queries to retrieve non-matching records from SQL Server tables. Adjust the queries as needed to suit specific database structures and requirements.