How to use SQL Server Left Join on Distinct

We will learn and comprehend how to use the SQL Server DISTINCT clause with the LEFT JOIN clause in this SQL Server tutorial.

I recently received the work update for utilizing the SQL Server DISTINCT and JOIN clauses in the Online Order Payment System. And I discovered that I must use the DISTINCT clause with the JOIN clause to provide unique records from the table. It can be accomplished using the SELECT JOIN query.

Here is the list of the topics that we are going to cover:

• How to use SQL Server Left Join on Distinct
• Using SQL Server Left Outer Join Distinct
• How to use SQL Server Left Join On Distinct In Where Clause

SQL Server Left Join on Distinct

Let’s see an example of the SQL Server DISTINCT clause with the LEFT JOIN clause on two tables in SQL Server using the following query:

EXAMPLE:

SELECT DISTINCT COUNTRIES.NAME,
USERS.FULL_NAME
FROM COUNTRIES
LEFT JOIN USERS
ON COUNTRIES.CODE=USERS.COUNTRY_CODE
WHERE USERS.FULL_NAME IS NOT NULL;

With the help of the LEFT JOIN clause, we have retrieved unique country names (United States of America, Australia ) and the user’s full name from both tables i.e; COUNTRIES and USERS tables. In the WHERE condition, we filter out the user’s full name which doesn’t carry the NULL values in the USERS table.

In other words, the SELECT statement retrieves all records from the COUNTRIES table (left_table) and with matching records from the USERS ( right_table) for the result set.

EXAMPLE_2:

SELECT DISTINCT COUNTRIES.NAME,
MERCHANTS.MERCHANT_NAME
FROM COUNTRIES
LEFT JOIN MERCHANTS
ON COUNTRIES.CODE=MERCHANTS.COUNTRY_CODE
WHERE MERCHANTS.MERCHANT_NAME IS NOT NULL;

With the help of the LEFT JOIN clause, the SELECT statement retrieves all unique country’s names based on the merchant’s name from both tables i.e.; COUNTRIES and MERCHANTS table.

So except these country names ( United States of America) are not having any merchant name based on the LEFT JOIN result set.

We hope that you have understood how to use SQL Server DISTINCT clause with the LEFT JOIN clause on tables by the query.

Read: SQL Server SELF JOIN 

SQL Server Left Outer Join Distinct

Before we go to the example of using tables with the SQL Server LEFT OUTER JOIN clause and the SQL Server DISTINCT clause. We must be aware that the SQL Server LEFT JOIN and SQL Server LEFT OUTER JOIN clauses are identical.

The OUTER keyword, which has been utilized with the LEFT JOIN clause, is the only change. Please look into this “SQL Server LEFT JOIN vs LEFT OUTER JOIN” subtopic to view an example.

Let’s see an example of how to use the SQL Server DISTINCT clause with the LEFT OUTER JOIN clause on tables in the following query:

EXAMPLE:

SELECT DISTINCT MERCHANTS.MERCHANT_NAME,
PRODUCTS.NAME
FROM MERCHANTS
LEFT OUTER JOIN PRODUCTS
ON MERCHANTS.ID=PRODUCTS.MERCHANT_ID;

With the help of the LEFT OUTER JOIN clause, the SELECT statement retrieves unique merchant names with their products and name from both tables i.e.; MERCHANTS and PRODUCTS table.

We hope that you have understood how to use SQL Server DISTINCT clause with the LEFT OUTER JOIN clause on tables by the query.

Read: SQL Server OUTER JOIN

SQL Server Left Join On Distinct In Where Clause

Here we will see how to use the SQL Server DISTINCT clause with the LEFT JOIN clause and WHERE condition on tables by the following query:

EXAMPLE:

SELECT DISTINCT PRODUCTS.NAME,
CATEGORIES.CAT_NAME
FROM PRODUCTS
LEFT JOIN CATEGORIES
ON PRODUCTS.CATEGORY_ID=CATEGORIES.ID
WHERE CATEGORIES.CAT_NAME !='Shirts';

The SELECT query obtains all product names based on the category name with the aid of the LEFT JOIN clause. However, under the WHERE condition, the product name Shirts should not appear in the result set due to the category name; instead, the remaining product names that include category names will appear in the result set.

We hope that you have understood how to use the SQL Server DISTINCT clause with LEFT JOIN and WHERE conditions on tables by the query.

You may also like to read the following SQL Server tutorials.

• SQL Server Count Join Group By
• Delete From With Join in SQL Server
• What is SQL Server Cross Join?
• SQL Server Left Join With Count

After reading this tutorial, we now know about the topic “SQL Server Left Join on Distinct.” To assist you in understanding the concept, here is a list of every subtopic that we have discussed.

• How to use SQL Server Left Join on Distinct
• Using SQL Server Left Outer Join Distinct
• How to use SQL Server Left Join On Distinct In Where Clause