SQL Server Inner Join Distinct

by
Sql server inner join distinct example

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

So, I got an update from the client of their Book Management System project for using the DISTINCT clause with the INNER JOIN clause on tables in the SQL Server. And we know that it can give unique values with the SQL Server INNER JOIN clause on tables by the query. The SQL Server SELECT JOIN query for the result set will accomplish this.

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

  • How to use SQL Server Inner Join Distinct
  • SQL Server Inner Join Distinct And Group By
  • Working of SQL Server Inner Join Distinct And Where Clause
  • What is SQL Server Inner Join Distinct Like Clause
  • How to use SQL Server Inner Join Distinct Multiple Tables
  • Using SQL Server Inner Join Distinct Row_Number
  • Working of SQL Server Inner Join Distinct Top 1
  • How to use SQL Server Inner Join Distinct Year

SQL Server Inner Join Distinct

The query in SQL Server uses the INNER JOIN clause to pick out records from both tables with matched rows. Let’s look at an example of a table query using the SQL Server DISTINCT clause and INNER JOIN clause:

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

The SELECT statement retrieves individual book titles depending on the category name of the books from both tables, BOOKS, and BOOK_CATEGORY, using the INNER JOIN clause.

Sql server inner join distinct example
Example of SQL Server INNER JOIN clause with the DISTINCT clause

We hope you now fully grasp the query’s use of the SQL Server DISTINCT clause along with the INNER JOIN clause on tables.

Read: SQL Server OUTER JOIN

SQL Server Inner Join Distinct And Group By

The SQL Server GROUP BY clause requires the usage of any aggregate functions, such as (MAX, MIN, SUM, AVG, etc). However, without using any aggregate function, we can not use the DISTINCT clause with the GROUP BY clause.

Here is an example of a SQL Server table with the DISTINCT clause, the INNER JOIN clause, the GROUP BY clause, and no aggregate function.

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

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 AUTHORS.AUTHOR_FULLNAME column from the AUTHORS table.

Sql server inner join distinct and group by error example
Error example of SQL Server INNER JOIN with the DISTINCT and GROUP BY clauses

EXAMPLE:

SELECT DISTINCT MAX(BOOKS.BOOK_PRICE) AS MAX_BOOKPRICE,
AUTHORS.AUTHOR_FULLNAME
FROM BOOKS
INNER JOIN AUTHORS
ON BOOKS.AUTHOR_ID=AUTHORS.AUTHOR_ID
GROUP BY AUTHORS.AUTHOR_FULLNAME
ORDER BY MAX_BOOKPRICE DESC;

The SELECT statement extracts individual book prices based on the maximum rate as well as the names of the book’s authors from both the BOOKS and AUTHORS tables using the INNER JOIN clause.

After classifying the books by authors, we utilized the BOOKS table’s ORDER BY clause to list the book prices in descending order.

Sql server inner join distinct and group by example
Example of SQL Server INNER JOIN clause with DISTINCT and GROUP BY clauses

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

Read: SQL Server Right Join Distinct

SQL Server Inner Join Distinct And Where Clause

Here we will see an example of the SQL Server INNER JOIN with DISTINCT clause and WHERE condition on tables in the following query:

EXAMPLE:

SELECT DISTINCT BOOKS.BOOK_PRICE,
AUTHORS.AUTHOR_FULLNAME
FROM BOOKS
INNER JOIN AUTHORS
ON BOOKS.AUTHOR_ID=AUTHORS.AUTHOR_ID
WHERE BOOKS.BOOK_PRICE >25;

With the help of the INNER JOIN clause in the SELECT statement, we retrieved the unique book price along with the author’s name from the BOOKS and AUTHORS table. And it is done by filtering the book price greater than 25 from the BOOK table in the WHERE condition.

Sql server inner join distinct and where clause example
Example of SQL Server Inner Join with Distinct and Where Clause

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

Read: SQL Server Count Join Group By

SQL Server Inner Join Distinct Like Clause

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

SELECT DISTINCT BOOK_CATEGORY.CATEGORY_NAME,
BOOKS.BOOK_NAME
FROM BOOK_CATEGORY
INNER JOIN BOOKS
ON BOOK_CATEGORY.BOOK_ID=BOOKS.BOOK_ID
WHERE BOOK_CATEGORY.CATEGORY_NAME LIKE '%d%';

In the query example, we retrieved the unique category name of the books with the book names from the BOOKS and BOOK_CATEGORY table.

For the result set, we have used the LIKE clause to get the category name which contains the letter ‘d’ in between in the CATEGORY_NAME column of the BOOK_CATEGORY table.

Sql server inner join distinct like clause example
Example of SQL Server INNER JOIN with the DISTINCT and LIKE clauses

We hope that you have understood the concept of using the SQL Server INNER JOIN clause with the DISTINCT clause on tables by the query.

Read: Delete From With Join in SQL Server

SQL Server Inner Join Distinct Multiple Tables

Here we will learn and understand how to use the SQL Server INNER JOIN clause with DISTINCT on multiple tables by the following query:

SELECT DISTINCT BOOKS.BOOK_NAME,
BOOK_CATEGORY.CATEGORY_NAME,
ORDERS.ORDER_ID
FROM BOOKS
INNER JOIN BOOK_CATEGORY
ON BOOKS.BOOK_ID=BOOK_CATEGORY.BOOK_ID
INNER JOIN ORDERS
ON BOOK_CATEGORY.BOOK_ID=ORDERS.BOOK_ID;

With the INNER JOIN clause, the SELECT statement retrieves unique book names along with the book category and order number from these tables i.e.; BOOKS and BOOK_CATEGORY and ORDERS tables.

Sql server inner join distinct multiple tables example
Example of SQL Server INNER JOIN with the DISTINCT clause on multiple tables

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

Read: What is SQL Server Cross Join?

SQL Server Inner Join Distinct Row_Number

In this SQL Server subtopic section, we will learn and understand how to use the SQL Server DISTINCT and ROW_NUMBER functions with the INNER JOIN on tables by the query.

EXAMPLE:

SELECT DISTINCT ROW_NUMBER() OVER( PARTITION BY BOOKS.BOOK_NAME 
ORDER BY BOOKS.BOOK_NAME DESC) AS BOOKS_PERAUTHOR,
AUTHORS.AUTHOR_FULLNAME
FROM BOOKS
INNER JOIN AUTHORS
ON BOOKS.AUTHOR_ID=AUTHORS.AUTHOR_ID;

With the help of the INNER JOIN clause in the SELECT statement, we have got a unique number of books per author’s name from both tables i.e.; BOOKS and AUTHORS table.

Sql server inner join distinct row_number example
Example of SQL Server INNER JOIN clause with DISTINCT ROW_NUMBER functions

Using the SQL Server INNER JOIN clause with the DISTINCT ROW NUMBER function on tables by the query, we hope you have comprehended the subtopic “SQL Server Inner Join Distinct Row Number.”

Read: SQL Server Left Join With Count

SQL Server Inner Join Distinct Top 1

An example of the SQL Server INNER JOIN clause using the DISTINCT TOP 1 on columns of tables is shown in the following query:

SELECT DISTINCT TOP 1 BOOK_CATEGORY.CATEGORY_NAME,
BOOKS.BOOK_NAME
FROM BOOK_CATEGORY
INNER JOIN BOOKS
ON BOOK_CATEGORY.BOOK_ID=BOOKS.BOOK_ID;

With the help of the SELECT statement in the INNER JOIN clause, we retrieved the first unique record of the category name with the book names from both tables i.e.; BOOK_CATEGORY and BOOKS table.

Sql server inner join distinct top 1 example
Example of SQL Server INNER JOIN with the DISTINCT TOP 1 clause

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

Read: SQL Server Left Join With Count

SQL Server Inner Join Distinct Year

We will see an example of a SQL Server INNER JOIN using the DISTINCT YEAR function on two tables i.e.; EMPLOYEES and TIMESHEET tables by the following query:

Sql server inner join distinct year image
EMPLOYEES table
Sql server inner join distinct year image 1
TIMESHEET table

EXAMPLE:

SELECT DISTINCT YEAR(TIMESHEET.CHECK_OUT) AS TIMESHEET_YEAR_VALUE,
EMPLOYEES.FIRST_NAME
FROM TIMESHEET
INNER JOIN EMPLOYEES
ON TIMESHEET.EMP_ID=EMPLOYEES.EMP_ID;

As we see in the above query example, we retrieved the unique year portion value of the CHECK_OUT column and also with the first name of employees from both tables i.e.; TIMESHEET and EMPLOYEES table.

Sql server inner join distinct year example
Example of SQL Server INNER JOIN with the DISTINCT YEAR functions

You should now be familiar with how to use the INNER JOIN clause in SQL Server with the DISTINCT YEAR function on the tables that you will be joining.

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

We now know the subject “SQL Server Select Distinct Value in Inner Join” after reading this lesson. Here is a list of each subtopic we covered to help you understand the concept.

  • How to use SQL Server Inner Join Distinct
  • SQL Server Inner Join Distinct And Group By
  • Working of SQL Server Inner Join Distinct And Where Clause
  • What is SQL Server Inner Join Distinct Like Clause
  • How to use SQL Server Inner Join Distinct Multiple Tables
  • Using SQL Server Inner Join Distinct Row_Number
  • Working of SQL Server Inner Join Distinct Top 1
  • How to use SQL Server Inner Join Distinct Year