SQL Server Right Outer Join vs Left Outer Join

by
SQL Server Right Outer Join vs Left Outer Join

In this SQL Server tutorial, we will understand the difference between the SQL Server RIGHT OUTER JOIN and LEFT OUTER JOIN clauses on tables by the query.

In SQL Server, there are different types of JOINS that we can use. RIGHT JOIN and LEFT JOIN are some of the most utilized JOINS in SQL Server. But, there is a lot of confusion between both these JOINS.

So, here we will understand the key difference between the SQL Server RIGHT JOIN and SQL Server LEFT JOIN. Moreover, we will discuss the following topics.

The subjects we’ll be covering are listed below:

  • Difference between SQL Server Left Outer Join & Right Outer Join
  • Difference between SQL Server Left Outer Join & Right Outer Join Distinct
  • Difference between SQL Server Left Outer Join & Right Outer Join Multiple Tables
  • Difference between SQL Server Left Outer Join & Right Outer Join Year
  • Difference between SQL Server Left Outer Join & Right Outer Join Greater Than

SQL Server Left Outer Join Vs Right Outer Join

In SQL Server, the LEFT OUTER JOIN clause returns all records from the left_table (TABLE_1) and the matching records from the right_table (TABLE_2). And in the RIGHT OUTER JOIN clause, it returns all records from the right_table (TABLE_1) and with matching records from the left_table (TABLE_2).

For your understanding, the LEFT OUTER JOIN and LEFT JOIN clause has the same definition and syntax. This goes the same with the RIGHT OUTER JOIN and RIGHT JOIN clause methods.

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

SELECT AUTHORS.*,
BOOKS.BOOK_NAME
FROM AUTHORS
LEFT OUTER JOIN BOOKS
ON AUTHORS.AUTHOR_ID=BOOKS.AUTHOR_ID;

With the help of the LEFT OUTER JOIN query example, we retrieve all records of the author’s details and their book’s name from both tables i.e., AUTHORS and BOOKS.

Sql server left outer join vs right outer join example
Example of SQL Server RIGHT OUTER JOIN clause

Here is an example of SQL Server RIGHT OUTER JOIN on tables by the following query:

SELECT AUTHORS.AUTHOR_FULLNAME,
BOOKS.*
FROM AUTHORS
RIGHT OUTER JOIN BOOKS
ON AUTHORS.AUTHOR_ID=BOOKS.AUTHOR_ID;

With the help of the RIGHT OUTER JOIN in the SELECT statement, we retrieve all records of the author’s name with their book’s detail from both tables i.e., AUTHORS and BOOKS.

Sql server left outer join vs right outer join sample example
Example of SQL Server RIGHT OUTER JOIN clause

We hope you understand the difference between the SQL Server LEFT OUTER JOIN and RIGHT OUTER JOIN clauses on tables by the query.

Read: SQL Server LEFT OUTER JOIN with WHERE clause

SQL Server Left Outer Join Vs Right Outer Join Distinct

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

Example of DISTINCT with LEFT OUTER JOIN:

SELECT DISTINCT BOOKS.BOOK_NAME,
BOOKS.BOOK_EDITION,
BOOK_CATEGORY.CATEGORY_NAME
FROM BOOKS
LEFT OUTER JOIN BOOK_CATEGORY
ON BOOKS.BOOK_ID=BOOK_CATEGORY.BOOK_ID
WHERE BOOK_CATEGORY.CATEGORY_NAME IS NOT NULL;

As a result of the LEFT OUTER JOIN in the SELECT statement, we can retrieve distinct book names, as well as their editions and the category name from both tables, i.e., BOOKS and BOOK_CATEGORY, using the WHERE condition.

For the WHERE condition, we filter the category name with a NOT NULL value so we can get the book’s name with its edition.

Sql server left outer join vs right outer join distinct sample example
Example of SQL Server DISTINCT with LEFT OUTER JOIN clause

Example of DISTINCT with RIGHT OUTER JOIN:

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

In the RIGHT OUTER JOIN query example, we retrieve the unique author’s name with their book’s name from both tables i.e. AUTHORS and BOOKS.

Sql server left outer join vs right outer join distinct example
Example of SQL Server DISTINCT with RIGHT OUTER JOIN clause

We hope you understand the use of SQL Server DISTINCT with the LEFT OUTER JOIN and RIGHT OUTER JOIN clauses on tables by the query.

Read: Delete From With Join in SQL Server

SQL Server Left Outer Join Vs Right Outer Join Greater Than

Here, we will see how to use the SQL Server GREATER THAN operator with the LEFT OUTER JOIN and RIGHT OUTER JOIN on tables which is shown in the following query.

Example of SQL Server LEFT OUTER JOIN with GREATER THAN:

SELECT ORDERS.ORDER_ID,
CUSTOMERS.CUSTOMER_FULLNAME,
CUSTOMERS.CURRENT_ADDRESS
FROM ORDERS
LEFT OUTER JOIN CUSTOMERS
ON ORDERS.CUSTOMER_ID=CUSTOMERS.CUSTOMER_ID
WHERE ORDERS.ORDER_ID >2;

With the help of the LEFT OUTER JOIN in the SELECT statement, we retrieve records of the customer’s order ID with name and address from both tables i.e.; ORDERS and CUSTOMERS by the WHERE condition.

In the WHERE condition, we filtered the order ID of the customer which is more than 2 from the ORDERS table. So that, we can get the order ID with the customer’s name and address in the resultset.

Sql server left outer join vs right outer join greater than sample example
Example of SQL Server LEFT OUTER JOIN with GREATER THAN operator

Example of RIGHT OUTER JOIN with GREATER THAN:

SELECT AUTHORS.AUTHOR_FULLNAME,
BOOKS.BOOK_NAME
FROM AUTHORS
RIGHT OUTER JOIN BOOKS
ON AUTHORS.AUTHOR_ID=BOOKS.AUTHOR_ID
WHERE BOOKS.BOOK_PRICE >15;

With the help of GREATER THAN in the RIGHT OUTER JOIN query example, we retrieve author’s with their book’s name from both tables i.e., AUTHORS and BOOKS by the WHERE condition.

In the WHERE condition, we filter the author’s book prices that are greater than $15. So that we can get retrieve the author’s name with their book in the resultset.

Sql server left outer join vs right outer join greater than example
Example of SQL Server RIGHT OUTER JOIN with GREATER THAN operator

We hope you understand the use of SQL Server LEFT OUTER JOIN and RIGHT OUTER JOIN with the GREATER THAN operator on tables by the query.

Read: SQL Server Left Join With Count

SQL Server Left Outer Join Vs Right Outer Join Multiple Tables

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

Example of SQL Server LEFT OUTER JOIN on multiple tables:

SELECT AUTHORS.AUTHOR_FULLNAME,
BOOKS.BOOK_NAME,
BOOK_CATEGORY.CATEGORY_NAME
FROM AUTHORS
LEFT OUTER JOIN BOOKS
ON AUTHORS.AUTHOR_ID=BOOKS.AUTHOR_ID
LEFT OUTER JOIN BOOK_CATEGORY
ON BOOKS.BOOK_ID=BOOK_CATEGORY.BOOK_ID;

With the help of the LEFT OUTER JOIN query example, we retrieve the author’s name with the book’s name and category’s name from these tables i.e., AUTHORS, BOOKS and BOOK_CATEGORY.

Sql server left outer join vs right outer join multiple tables sample example
Example of SQL Server LEFT OUTER JOIN on multiple tables

Example of SQL Server RIGHT OUTER JOIN on multiple tables:

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

In the RIGHT OUTER JOIN query example, we retrieve all records of the customer’s name with their book’s name and order’s ID from these tables i.e., CUSTOMERS, BOOKS, and ORDERS by the WHERE condition.

In the WHERE condition, we filtered the customer’s name by a NOT NULL value. So that we can get other customer’s name who have ordered their book’s name and order ID in the resultset.

Sql server left outer join vs right outer join multiple tables example
Example of SQL Server RIGHT OUTER JOIN on multiple tables

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

Read: How To Update Table Using JOIN in SQL Server

SQL Server Left Outer Join Vs Right Outer Join Year

We will learn and understand how to use the SQL Server YEAR function with a LEFT OUTER JOIN and RIGHT OUTER JOIN on tables by the query.

Example of SQL Server YEAR function with LEFT OUTER JOIN:

 SELECT EMPLOYEES.FIRST_NAME+ ' ' +EMPLOYEES.LAST_NAME AS FULL_NAME,
 EMPLOYEE_CREDENTIALS.LOGIN_ID
 FROM EMPLOYEES
 LEFT OUTER JOIN EMPLOYEE_CREDENTIALS
 ON EMPLOYEES.EMP_ID=EMPLOYEE_CREDENTIALS.EMP_ID
 WHERE EMPLOYEE_CREDENTIALS.LOGIN_ID IS NOT NULL;

With the LEFT OUTER JOIN in the SELECT statement, we added the employee’s first and last name to their full name. And also retrieve the employee’s login ID from both tables: EMPLOYEES and EMPLOYEE_CREDENTIALS by the WHERE condition.

In the WHERE condition, we filter the employee login ID with a NOT NULL value. By adding and retrieving the login ID created in the resultset, we can get employee’s full name.

Sql server left outer join vs right outer join year sample example
Example of SQL Server YEAR function with LEFT OUTER JOIN clause

Example of SQL Server YEAR function with the RIGHT OUTER JOIN:

 SELECT YEAR(TIMESHEET.CHECK_IN) AS CHECK_INTIME,
 EMPLOYEES.FIRST_NAME
 FROM TIMESHEET
 RIGHT OUTER JOIN EMPLOYEES
 ON TIMESHEET.EMP_ID=EMPLOYEES.EMP_ID
 WHERE YEAR(TIMESHEET.CHECK_IN) IS NOT NULL;

With the help of the RIGHT OUTER JOIN query example, we extracted the year portion value of employee check-in time. Then we retrieve the employee’s first name from both tables i.e., TIMESHEET and EMPLOYEES by the WHERE condition.

In the WHERE condition, we filter the YEAR function value by NOT NULL. This will bring year value with the employee’s first name in the resultset.

Sql server left outer join vs right outer join year example
Example of SQL Server YEAR function with RIGHT OUTER JOIN clause

We comprehend you understand the use of SQL Server YEAR function with the LEFT OUTER JOIN and RIGHT OUTER JOIN on tables by the query.

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

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

  • Difference between SQL Server Left Outer Join & Right Outer Join
  • Difference between SQL Server Left Outer Join & Right Outer Join Distinct
  • Difference between SQL Server Left Outer Join & Right Outer Join Multiple Tables
  • Difference between SQL Server Left Outer Join & Right Outer Join Year
  • Difference between SQL Server Left Outer Join & Right Outer Join Greater Than