How to use Distinct in JOIN in SQL Server

In this SQL Server tutorial, we will learn and understand how to use the SQL Server DISTINCT clause with the JOIN clause on tables by the query.

Recently, I was working on Book Management System Database. And I got a requirement from the client on using the SQL Server DISTINCT clause with the JOIN clause on tables by the query. This can be accomplished by using SQL Server SELECT JOIN statement.

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

• How to use Distinct in Join in SQL Server
• How to use Distinct in Left Join in SQL Server
• How to use Distinct in Join in SQL Server for 3 tables
• How to use Distinct in Join in SQL Server in where clause
• How to use Distinct in Join in SQL Server multiple columns
• How to use Distinct in Join in SQL Server using group by

Here is the ER diagram of the Book Management System which will be used with the DISTINCT clause with the JOIN clause:

Here are the images of the MASTER_TABLE i.e.; (BOOKS, CUSTOMERS, BOOK_CATEGORY, AUTHORS) and one TRANSCATIONAL_TABLE i.e.; ( ORDERS) which are shown below:

Distinct in Join in SQL Server

Based on a shared column between two or more tables, a JOIN clause is used to merge rows from those tables. If you want more information about the JOIN clause and its type then kindly look at these posts.

Let’s see an example of SQL Server DISTINCT clause with the JOIN clause on tables by the following query:

EXAMPLE:

SELECT DISTINCT AUTHORS.AUTHOR_FULLNAME,
BOOKS.BOOK_NAME
FROM AUTHORS
INNER JOIN BOOKS
ON AUTHORS.AUTHOR_ID=BOOKS.AUTHOR_ID;

With the help of the INNER JOIN clause, the SELECT statement retrieves unique author names with their book names from both tables i.e.; AUTHORS and BOOKS tables.

We hope you understand how to use SQL Server DISTINCT clause with the JOIN clause on tables by the query.

Read: MySQL vs SQL Server

Distinct in Left Join in SQL Server

The SQL Server LEFT JOIN clause is used to retrieve all records from the left_table (TABLE_1) and with matching records from the right_table (TABLE_2). For more information, kindly look into the “SQL Server LEFT JOIN” post.

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

EXAMPLE:

SELECT DISTINCT BOOK_CATEGORY.CATEGORY_NAME,
BOOKS.BOOK_NAME
FROM BOOK_CATEGORY
LEFT JOIN BOOKS
ON BOOK_CATEGORY.BOOK_ID=BOOKS.BOOK_ID;

With the help of the LEFT JOIN clause, the SELECT statement retrieves records of book names based on the unique book category name from both tables i.e.; BOOKS and BOOK_CATEGORY table.

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

Read: Delete From With Join in SQL Server

Distinct in Join in SQL Server for 3 tables

Here we will see an example of the SQL Server DISTINCT clause with the JOIN clause on several tables by the following query:

EXAMPLES:

SELECT DISTINCTBOOKS.BOOK_NAME,
CUSTOMERS.CUSTOMER_FULLNAME,
ORDERS.ORDER_ID
FROM BOOKS
INNER JOIN ORDERS
ON BOOKS.BOOK_ID=ORDERS.BOOK_ID
INNER JOIN CUSTOMERS
ON CUSTOMERS.CUSTOMER_ID=ORDERS.CUSTOMER_ID;

With the help of the INNER JOIN clause, the SELECT statement retrieves unique books’ names based on the customer’s full name and order id from these tables i.e.; BOOKS, ORDERS and CUSTOMERS table.

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

Read: What is SQL Server Cross Join?

Distinct in Join in SQL Server in where clause

Here is an example of the SQL Server DISTINCT clause with the JOIN clause and the WHERE condition on tables by the following query:

EXAMPLE:

SELECT DISTINCT BOOKS.BOOK_NAME,
BOOK_CATEGORY.CATEGORY_NAME
FROM BOOKS
INNER JOIN BOOK_CATEGORY
ON BOOKS.BOOK_ID=BOOK_CATEGORY.BOOK_ID
WHERE BOOKS.BOOK_PRICE>=15;

With the help of the INNER JOIN clause, the SELECT statement retrieves all records of unique book names based on category names whose price is greater or equal to 15 from both tables i.e.; BOOKS and BOOK_CATEGORY tables.

we hope that you have understood how to use the SQL Server DISTINCT clause with the INNER JOIN clause with the WHERE condition on two tables by the query.

Read: SQL Server Outer Join With Count

Distinct in Join in SQL Server multiple columns

In this subtopic, we will see an example of distinct multiple columns with the JOIN clause of tables by the following query:

EXAMPLE:

SELECT DISTINCT AUTHORS.AUTHOR_FULLNAME,
BOOKS.AUTHOR_ID
FROM AUTHORS
INNER JOIN BOOKS
ON AUTHORS.AUTHOR_ID=BOOKS.AUTHOR_ID;

With the help of the INNER JOIN clause, the SELECT statement retrieves all unique authors’ names with unique author’s id from both tables i.e.; AUTHORS and BOOKS tables.

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

Read: How to use SQL Server Left Join on Distinct

Distinct in Join in SQL Server using group by

If we want to use the SQL Server GROUP BY clause, we need to use any aggregate functions like (MAX, MIN, SUM, AVG, etc). However, we can not use the DISTINCT clause with the GROUP BY clause without using any aggregate function.

Let’s see an example of SQL Server DISTINCT clause with the JOIN clause and GROUP BY clause on tables but without the aggregate function:

SELECT DISTINCT BOOKS.BOOK_PRICE,
BOOK_CATEGORY.CATEGORY_NAME
FROM BOOKS
INNER JOIN BOOK_CATEGORY
ON BOOKS.BOOK_ID=BOOK_CATEGORY.BOOK_ID
GROUP BY BOOK_CATEGORY.CATEGORY_NAME;

In this query result set, we get a syntax error because of not using the aggregate function (max, min, etc.) on the BOOK_PRICE column of the BOOKS table. And to get the query output, we have grouped the records of the CATEGORY_NAME column from the BOOK_CATEGORY table.

EXAMPLE:

SELECT MAX(DISTINCT BOOKS.BOOK_PRICE) AS MAX_VALUE,
BOOK_CATEGORY.CATEGORY_NAME
FROM BOOKS
INNER JOIN BOOK_CATEGORY
ON BOOKS.BOOK_ID=BOOK_CATEGORY.BOOK_ID
GROUP BY BOOK_CATEGORY.CATEGORY_NAME;

With the help of aggregate_functions, the SELECT statement retrieves the max book category name based on the book names from both tables i.e.; BOOK_CATEGORY and BOOKS table.

We hope that you have understood the subtopic “Distinct in Join SQL Server using group by” by using the SQL Server DISTINCT clause with JOIN and GROUP BY clauses on tables by the query.

Also, take a look at some SQL Server tutorials.

• SQL Server Inner Join Top 1
• SQL Server Inner Join With Where Clause
• How To Update Table Using JOIN in SQL Server
• Msg 3609 the transaction ended in the trigger
• View line numbers in SQL Server Management Studio

We now know about the post “How to use Distinct in JOIN query in SQL Server” after reading this tutorial. Here is a list of each subtopic we covered to help you understand the concept.

• How to use Distinct in Join in SQL Server
• How to use Distinct in Left Join in SQL Server
• How to use Distinct in Join in SQL Server for 3 tables
• How to use Distinct in Join in SQL Server in where clause
• How to use Distinct in Join in SQL Server multiple columns
• How to use Distinct in Join in SQL Server using group by