SQL Server Right Outer Join Multiple Tables

In this SQL Server tutorial, we will learn the use of SQL Server RIGHT OUTER JOIN on multiple tables by the query.

In SQL Server, different types of JOINS can be used on tables. And there isn’t a difference between the SQL Server RIGHT JOIN and RIGHT OUTER JOIN. Both clauses have the same method and syntax but the only difference is using the OUTER keyword in between for tables in the query.

So, here we will understand the use of SQL Server RIGHT OUTER JOIN clause on multiple tables by the query. Moreover, we will discuss this in detail in the subtopic.

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

• Working of SQL Server Right Outer Join Multiple Tables
• How to use SQL Server Right Outer Join Multiple Tables And Where Clause
• How do we use SQL Server Right Outer Join Multiple Tables Between
• Use of SQL Server Right Outer Join Multiple Tables Count
• How to use SQL Server Right Outer Join Multiple Tables Distinct
• How do we use SQL Server Right Outer Join Multiple Tables Group By
• How to use SQL Server Right Outer Join Multiple Tables Like
• Use of SQL Server Right Outer Join Multiple Tables Multiple Columns
• How do we use SQL Server Right Outer Join Multiple Tables Row_Count
• How to use SQL Server Right Outer Join Multiple Tables Year

SQL Server Right Outer Join Multiple Tables

Here we will see an example of SQL Server RIGHT OUTER JOIN on multiple tables by the query.

EXAMPLE:

SELECT COUNTRIES.COUNTRY_NAME,
STATES.STATE_NAME,
CITY.CITY_NAME
FROM COUNTRIES
RIGHT OUTER JOIN STATES
ON COUNTRIES.COUNTRY_ID=STATES.COUNTRY_ID
RIGHT OUTER JOIN CITY
ON STATES.STATE_ID=CITY.STATE_ID;

With the help of the RIGHT OUTER JOIN query example, we retrieve all records of the country’s name with their state’s name and city’s name from tables: COUNTRIES, STATES, and CITY.

We hope you understand the use of SQL Server RIGHT OUTER JOIN on multiple tables by the query.

Read: What is SQL Server Cross Join?

SQL Server Right Outer Join Multiple Tables And Where Clause

Here we will see an example of SQL Server RIGHT OUTER JOIN with the WHERE clause on multiple tables by the query.

EXAMPLE:

SELECT COUNTRIES.COUNTRY_NAME,
STATES.STATE_NAME,
CITY.CITY_NAME
FROM COUNTRIES
RIGHT OUTER JOIN STATES
ON COUNTRIES.COUNTRY_ID=STATES.COUNTRY_ID
RIGHT OUTER JOIN CITY
ON STATES.STATE_ID=CITY.STATE_ID
WHERE COUNTRIES.COUNTRY_NAME='United States of America';

With the help of the RIGHT OUTER JOIN in the SELECT statement, we retrieve all records of the country’s name with their state’s name and city’s name from these tables i.e., COUNTRIES, STATES, and CITY by the WHERE condition.

In the WHERE condition, we filter the country name as the United States of America to see their state’s and city’s names in the resultset.

We hope you understand the use of the SQL Server RIGHT OUTER JOIN with the WHERE clause on multiple tables by the query.

Read: What is SQL Server Cross Join?

SQL Server Right Outer Join Multiple Tables Between

Here we will see an example of SQL Server RIGHT OUTER JOIN with the BETWEEN operator on multiple tables by the query.

EXAMPLE:

SELECT COUNTRIES.COUNTRY_NAME,
STATES.STATE_NAME,
CITY.CITY_NAME
FROM COUNTRIES
RIGHT OUTER JOIN STATES
ON COUNTRIES.COUNTRY_ID=STATES.COUNTRY_ID
RIGHT OUTER JOIN CITY
ON STATES.STATE_ID=CITY.STATE_ID
WHERE CITY.CITY_NAME BETWEEN 'Amsterdam' AND 'Hackney';

With the help of the RIGHT OUTER JOIN query example, we retrieve all records of the country’s name with their state’s name and city’s name from these tables COUNTRIES, STATES and CITY by the WHERE condition.

In the WHERE condition, we filter the city’s name between ‘Amsterdam’ and ‘Hackney’. So that, we can get other countries’ names and states’ names in the resultset.

We comprehend the example of SQL Server RIGHT OUTER JOIN with the BETWEEN operator on multiple tables by the query.

Read: SQL Server Left Join With Count

SQL Server Right Outer Join Multiple Tables Count

In this SQL Server subtopic section, we will see an example of the SQL Server COUNT function with the RIGHT OUTER JOIN on multiple tables by the query.

EXAMPLE:

SELECT COUNT(CITY.CITY_NAME) AS CITY_PERCOUNTRY,
STATES.STATE_NAME,
COUNTRIES.COUNTRY_NAME
FROM CITY
RIGHT OUTER JOIN STATES
ON CITY.STATE_ID=STATES.STATE_ID
RIGHT OUTER JOIN COUNTRIES
ON STATES.COUNTRY_ID=COUNTRIES.COUNTRY_ID
GROUP BY STATES.STATE_NAME,
COUNTRIES.COUNTRY_NAME
HAVING COUNT(CITY.CITY_NAME)> 1;

With the help of the COUNT function with the SELECT statement, we counted the city’s name based on the state’s name and country’s name from tables: CITY, STATES and COUNTRIES by the HAVING clause.

In the HAVING clause, we counted the city’s name whose value was greater than 1. So that, we retrieve the state’s name and country’s name in the resultset.

By now, you should clearly understand how to use the SQL Server COUNT function with the RIGHT OUTER JOIN clause on multiple tables.

Read: SQL Server Outer Join With Count

SQL Server Right Outer Join Multiple Tables Distinct

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

EXAMPLE:

SELECT DISTINCT COUNTRIES.COUNTRY_NAME,
STATES.STATE_NAME,
CITY.CITY_NAME
FROM COUNTRIES
RIGHT OUTER JOIN STATES
ON COUNTRIES.COUNTRY_ID=STATES.COUNTRY_ID
RIGHT OUTER JOIN CITY
ON STATES.STATE_ID=CITY.STATE_ID;

With the help of the RIGHT OUTER JOIN in the SELECT statement, we retrieved unique country’s name with their state’s name and city’s name from these tables i.e., COUNTRIES, STATES, and CITY.

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

Read: How To Update Table Using JOIN in SQL Server

SQL Server Right Outer Join Multiple Tables Like

Here we will see an example of SQL Server RIGHT OUTER JOIN with the LIKE clause on multiple tables by the query.

SELECT COUNTRIES.COUNTRY_NAME,
STATES.STATE_NAME,
CITY.CITY_NAME
FROM COUNTRIES
RIGHT OUTER JOIN STATES
ON COUNTRIES.COUNTRY_ID=STATES.COUNTRY_ID
RIGHT OUTER JOIN CITY
ON STATES.STATE_ID=CITY.STATE_ID
WHERE STATES.STATE_NAME LIKE 'N%';

In the RIGHT OUTER JOIN query example, the WHERE condition is used to obtain every record that contains the name of a country along with the name of its state and city.

In the WHERE condition, we filter out states whose names begin with the letter ‘N’. Using this approach, all records containing the country’s name and the city’s name will be displayed.

We hope you understand the use of SQL Server RIGHT OUTER JOIN with the LIKE clause on multiple tables by the query.

Read: SQL Server SELF JOIN 

SQL Server Right Outer Join Multiple Tables Multiple Columns

In this SQL Server subtopic section, we will see an example of SQL Server RIGHT OUTER JOIN on multiple columns of multiple tables by the query.

SELECT COUNTRIES.COUNTRY_NAME,
COUNTRIES.CONTIENT,
STATES.STATE_NAME,
CITY.CITY_NAME
FROM COUNTRIES
RIGHT OUTER JOIN STATES
ON COUNTRIES.COUNTRY_ID=STATES.COUNTRY_ID
RIGHT OUTER JOIN CITY
ON STATES.STATE_ID=CITY.STATE_ID;

With the help of the RIGHT OUTER JOIN in the SELECT statement, we retrieve all records of the countries’ detail with states and cities from these tables i.e., COUNTRIES, STATES and CITY.

We hope you fully understand how the query uses a SQL Server RIGHT OUTER JOIN on various columns across different tables.

Read: SQL Server Inner Join Top 1

SQL Server Right Outer Join Multiple Tables Row_Number

We will see an example of SQL Server ROW_NUMBER function with the RIGHT OUTER JOIN on multiple tables.

SELECT ROW_NUMBER()
OVER (PARTITION BY CITY.CITY_NAME
ORDER BY CITY.CITY_NAME DESC) AS CITY_NUMBER,
STATES.STATE_NAME,
COUNTRIES.COUNTRY_NAME
FROM CITY
RIGHT OUTER JOIN STATES
ON CITY.STATE_ID=STATES.STATE_ID
RIGHT OUTER JOIN COUNTRIES
ON STATES.COUNTRY_ID=COUNTRIES.COUNTRY_ID;

We partitioned and sorted the city name using the ROW_NUMBER function in the RIGHT OUTER JOIN query example. Then we retrieve the records of the state’s name with the country’s name from these tables: CITY, STATES, and COUNTRIES.

We hope you understand how ROW_NUMBER works with RIGHT OUTER JOINS on multiple tables in SQL Server.

Read: SQL Server INNER JOIN Tutorial

SQL Server Right Outer Join Multiple Tables Year

Here is an example of an SQL Server YEAR function with the RIGHT OUTER JOIN on multiple tables.

EXAMPLE:

SELECT YEAR(EMPLOYEES.DATE_OF_BIRTH) AS EMPLOYEES_DOB,
EMPLOYEE_CREDENTIALS.LOGIN_ID,
TIMESHEET.CHECK_IN
FROM EMPLOYEES
RIGHT OUTER JOIN EMPLOYEE_CREDENTIALS
ON EMPLOYEES.EMP_ID=EMPLOYEE_CREDENTIALS.EMP_ID
RIGHT OUTER JOIN TIMESHEET
ON EMPLOYEES.EMP_ID=TIMESHEET.EMP_ID
WHERE YEAR(EMPLOYEES.DATE_OF_BIRTH) IS NOT NULL
AND EMPLOYEE_CREDENTIALS.LOGIN_ID IS NOT NULL;

With the help of the RIGHT OUTER JOIN in the SELECT statement, we extracted the year portion value of the employee’s DOB and kept the records under the new EMPLOYEES_DOB column.

Then we retrieve the records of the employee’s login ID and check in time from these tables i.e., EMPLOYEES, EMPLOYEE_CREDENTIAL and TIMESHEET for the resultset.

This query uses the SQL Server YEAR function with the RIGHT OUTER JOIN on multiple tables.

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

• Introduction to SQL Server Trigger
• Instead of Trigger In SQL Server
• SQL Server Drop Trigger If Exists

We now know the subject “SQL Server Right Outer Join Multiple Tables” after reading this lesson. To assist you in comprehending the idea, we explored the following subtopics in detail.

• Working of SQL Server Right Outer Join Multiple Tables
• How to use SQL Server Right Outer Join Multiple Tables And Where Clause
• How do we use SQL Server Right Outer Join Multiple Tables Between
• Use of SQL Server Right Outer Join Multiple Tables Count
• How to use SQL Server Right Outer Join Multiple Tables Distinct
• How do we use SQL Server Right Outer Join Multiple Tables Group By
• How to use SQL Server Right Outer Join Multiple Tables Like
• Use of SQL Server Right Outer Join Multiple Tables Multiple Columns
• How do we use SQL Server Right Outer Join Multiple Tables Row_Count
• How to use SQL Server Right Outer Join Multiple Tables Year