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:

Distinct in join Sql server example
ER Diagram of Book Management System

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 Sql server image
BOOKS table
Distinct in join Sql server image 1
CUSTOMERS table
Distinct in join Sql server image 2
BOOK_CATEGORY table
Distinct in join Sql server image 3
AUTHORS table
Distinct in join Sql server image 4
ORDERS table ( TRANSACTIONAL table)

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.

Distinct in join Sql server
Example of SQL Server DISTINCT clause with JOIN clause

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.

Distinct in left join Sql server example
Example of SQL Server DISTINCT clause with the LEFT JOIN clause

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.

Distinct in join Sql server for 3 tables example
Example of Distinct in Join SQL Server For 3 Tables

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.

Distinct in join Sql server in where clause example
Example of Distinct in Join SQL Server in Where Clause

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.

Distinct in join Sql server for multiple columns example
Example of Distinct in Join SQL Server Multiple Columns

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.

Distinct in join Sql server using group by error example
Error example of Distinct in Join SQL Server Using Group By

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.

Distinct in join Sql server using group by example
Example of SQL Server Distinct in Join SQL Server Using Group By

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.

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