SQL Server Right Join Distinct

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

Recently, I was working again on the Book Management System Database where I got the information from the client that I have to use SQL Server RIGHT JOIN clause with the DISTINCT clause in SQL Server.

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

• How to use SQL Server Right Join Distinct
• How to use SQL Server Right Join Distinct And Where Clause
• How to use SQL Server Right Join Distinct Group By
• How to use SQL Server Right Join Distinct Having
• Using SQL Server Right Join Distinct Multiple Tables
• How to use SQL Server Right Join Distinct Year

SQL Server Right Join Distinct

All records from the right table (table_2) and the corresponding records from the left table (table_1) are returned via the SQL Server RIGHT JOIN clause. If there is no match, there are no records from the left side.

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

EXAMPLE:

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

With the help of the RIGHT JOIN clause, the SELECT statement retrieves unique book editions with name and author name from both tables i.e; AUTHORS and BOOKS table.0

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

Read: How to Create a Database in SQL Server 2019

SQL Server Right Join Distinct And Where Clause

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

EXAMPLE:

SELECT DISTINCT BOOKS.BOOK_EDITION,BOOKS.BOOK_NAME,
AUTHORS.AUTHOR_FULLNAME
FROM AUTHORS
RIGHT JOIN BOOKS
ON AUTHORS.AUTHOR_ID=BOOKS.AUTHOR_ID
WHERE BOOKS.BOOK_EDITION='II';

In this query, the SELECT statement retrieves the unique book edition with the name and the author name from both tables i.e; AUTHORS and BOOKS tables. But in the resultset, the WHERE condition will bring only those records of books whose book edition is second from the BOOKS table.

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

Read: Arithmetic operators in SQL Server

SQL Server Right Join Distinct 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 RIGHT JOIN clause and GROUP BY clause on tables but without the aggregate function:

SELECT DISTINCT BOOKS.BOOK_PRICE,
BOOK_CATEGORY.CATEGORY_NAME
FROM BOOK_CATEGORY
RIGHT JOIN BOOKS
ON BOOK_CATEGORY.BOOK_ID=BOOKS.BOOK_ID
WHERE BOOK_CATEGORY.CATEGORY_NAME IS NOT NULL
GROUP BY BOOK_CATEGORY.CATEGORY_NAME
ORDER BY MAX_PRICE DESC;

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 and also arranged the records into the descending order of the column from the BOOK_CATEGORY table.

EXAMPLE:

SELECT DISTINCT MAX (BOOKS.BOOK_PRICE) AS MAX_PRICE,
BOOK_CATEGORY.CATEGORY_NAME
FROM BOOK_CATEGORY
RIGHT JOIN BOOKS
ON BOOK_CATEGORY.BOOK_ID=BOOKS.BOOK_ID
WHERE BOOK_CATEGORY.CATEGORY_NAME IS NOT NULL
GROUP BY BOOK_CATEGORY.CATEGORY_NAME
ORDER BY MAX_PRICE DESC;

With the help of the RIGHT JOIN clause, the SELECT statement retrieves the unique maximum value of the book price along with the category name of both tables i.e.; BOOK_CATEGORY and BOOK tables.

In the WHERE clause, we have filtered the category name records as NULL, grouped the category name entries, and also organized the book price data for the result set in decreasing order.

We trust you now understand how to utilize the SQL Server RIGHT JOIN with the DISTINCT and GROUP BY clauses on tables by the query.

Read: SQL Server Replace Function

SQL Server Right Join Distinct Having

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

EXAMPLE:

SELECT DISTINCT MAX (BOOKS.BOOK_PRICE) AS MAX_PRICE,
BOOK_CATEGORY.CATEGORY_NAME
FROM BOOK_CATEGORY
RIGHT JOIN BOOKS
ON BOOK_CATEGORY.BOOK_ID=BOOKS.BOOK_ID
WHERE BOOK_CATEGORY.CATEGORY_NAME IS NOT NULL
GROUP BY BOOK_CATEGORY.CATEGORY_NAME
HAVING BOOK_CATEGORY.CATEGORY_NAME LIKE '%A%'
ORDER BY MAX_PRICE DESC;

With the help of the RIGHT JOIN clause in the SELECT statement, we retrieved the unique and maximum book price along with the category name from both these tables i.e; BOOK_CATEGORY and BOOKS table.

In the WHERE clause, we have filtered the category name entries that do not include a NULL value and also grouped the category name records whose names end in the letter A. Finally, we have sorted the result set’s entries of the book’s maximum price in descending order.

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

Read: SQL Server stored procedure insert into

SQL Server Right Join Distinct Multiple Tables

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

EXAMPLE:

SELECT DISTINCT BOOKS.BOOK_NAME,
CUSTOMERS.CUSTOMER_FULLNAME,
ORDERS.ORDER_ID
FROM CUSTOMERS
RIGHT JOIN ORDERS
ON CUSTOMERS.CUSTOMER_ID=ORDERS.CUSTOMER_ID
RIGHT JOIN BOOKS
ON ORDERS.BOOK_ID=BOOKS.BOOK_ID
WHERE CUSTOMERS.CUSTOMER_FULLNAME IS NOT NULL;

With the RIGHT JOIN clause, the SELECT statement retrieves unique book names along with customers’ names and order id from tables i.e; CUSTOMERS, ORDERS, and BOOKS table.

But in the WHERE condition, we have filtered the records of the customer’s full name by the Non NULL value from the CUSTOMER table for the result set.

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

Read: What is a View in SQL Server

SQL Server Right Join Distinct Year

The SQL Server YEAR function extracts the year portion value from the expression or column_name by the query. Let’s an example of the SQL Server DISTINCT YEAR function with the RIGHT JOIN clause on tables by the query.

Here are two tables i.e.; EMPLOYEES and EMPLOYEES_CREDENTIALS table for the query:

EXAMPLE:

SELECT DISTINCT YEAR(EMPLOYEES.DATE_OF_BIRTH) AS YEAR_VALUE,
EMPLOYEES.FIRST_NAME,
EMPLOYEE_CREDENTIALS.LOGIN_ID
FROM EMPLOYEE_CREDENTIALS
RIGHT JOIN EMPLOYEES
ON EMPLOYEE_CREDENTIALS.EMP_ID=EMPLOYEES.EMP_ID;

With the help of the RIGHT JOIN clause, the SELECT statement extract year portion value from the DATE_OF_BIRTH column and retrieves unique records of the employee’s DOB with first name and login id from both tables i.e.; EMPLOYEE_CREDENTIALS and EMPLOYEE table.

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

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

• SQL Server INNER JOIN Tutorial
• SQL Server LEFT JOIN Tutorial
• SQL Server Left Join With Count
• SQL Server Right Outer Join Multiple Tables
• SQL Server Outer Join With Count

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

• How to use SQL Server Right Join Distinct
• How to use SQL Server Right Join Distinct And Where Clause
• How to use SQL Server Right Join Distinct Group By
• How to use SQL Server Right Join Distinct Having
• Using SQL Server Right Join Distinct Multiple Tables
• How to use SQL Server Right Join Distinct Year