SQL Server Left Outer Join Multiple Tables

In this SQL Server tutorial, we will learn and understand how to use the SQL Server LEFT OUTER JOIN on multiple tables by query.

In one of our previous tutorials on SQL Server Outer Join, we have seen how to use the Outer join on 2 tables in SQL Server. However, there could be a situation where we need to use the SQL Server Outer Join on multiple tables.

So, this tutorial will focus on how to use the Outer join on more than 2 tables in SQL Server. Here is the list of topics that we are going to cover:

• Working of SQL Server Left Outer Join Multiple Tables
• Use of SQL Server Left Outer Join Multiple Tables And Where Clause
• How to use SQL Server Left Outer Join Multiple Tables Between
• How to use SQL Server Left Outer Join Multiple Tables Count
• Use of SQL Server Left Outer Join Multiple Tables Distinct
• How to use SQL Server Left Outer Join Multiple Tables Group By
• How to use SQL Server Left Outer Join Multiple Tables Having Joins
• How to use SQL Server Left Outer Join Multiple Tables Like
• How to use SQL Server Left Outer Join Multiple Tables Row Count

SQL Server Left Outer Join Multiple Tables

There is a difference between the SQL Server LEFT JOIN and LEFT OUTER JOIN clause using the OUTER keyword in between. Apart from the difference, the rest of the definition and syntax of the SQL Server LEFT JOIN and LEFT OUTER JOIN clauses is the same.

If you want to look into the example, then look into the “SQL Server Left Join vs Left Outer Join” subtopic.

In SQL Server, the LEFT JOIN clause is used to retrieve matching records from the left_table (table_1) and the matching records from the right_table (table_2). Let’s see an example of SQL Server LEFT OUTER JOIN on multiple tables by the following query.

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
WHERE BOOKS.BOOK_NAME IS NOT NULL
AND BOOK_CATEGORY.CATEGORY_NAME IS NOT NULL;

With the help of the LEFT OUTER JOIN query example, we retrieve all records of the author’s full name based on the book’s name and category’s name from these tables i.e.; AUTHORS, BOOKS and BOOK_CATEGORY by the WHERE condition.

In the WHERE condition, we filtered the record of the book’s name which doesn’t carry the NOT NULL value, and also the book category’s name with a NOT NULL value for the resultset.

We hope you understand how to use the SQL Server LEFT OUTER JOIN on multiple tables by the query.

Read: SQL Server LEFT OUTER JOIN with WHERE clause

SQL Server Left Outer Join Multiple Tables And Where Clause

Let’s see an example of SQL Server LEFT OUTER JOIN with the WHERE clause on multiple tables by the query.

EXAMPLE:

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
WHERE BOOKS.BOOK_NAME='A Great Gatsby';

With the help of the LEFT OUTER JOIN in the SELECT statement, we retrieve all records of the author’s name with his/ her book’s name and category from these tables i.e.; AUTHORS, BOOKS, and BOOK_CATEGORY which is based on the WHERE clause.

In the WHERE clause, we filter the book name as “A Great Gatsby” to get its author’s name and book category name for the resultset.

We hope you understand how to use the SQL Server LEFT OUTER JOIN with the WHERE clause on multiple tables by the query.

Read: SQL Server Left Join With Count

SQL Server Left Outer Join Multiple Tables Between

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

EXAMPLE:

SELECT ORDERS.ORDER_ID,
BOOKS.BOOK_NAME,
CUSTOMERS.CUSTOMER_FULLNAME
FROM ORDERS
LEFT OUTER JOIN BOOKS
ON ORDERS.BOOK_ID=BOOKS.BOOK_ID
LEFT OUTER JOIN CUSTOMERS
ON ORDERS.CUSTOMER_ID=CUSTOMERS.CUSTOMER_ID
WHERE ORDERS.ORDER_ID BETWEEN 2 AND 5;

Through the LEFT OUTER JOIN in the SELECT statement, we retrieve all book orders made by customers based on the order ID, book name, and customer name from these tables (ORDERS, BOOKS, and CUSTOMERS) based on the WHERE condition.

We filter the order number that is between 2 and 5 from the ORDERS table using the WHERE condition. This will allow us to include both the customer’s name and the book’s name in the resultset.

We hope you understand SQL Server LEFT OUTER JOIN with the BETWEEN operator on multiple tables by the query.

Read: How to use SQL Server Left Join on Distinct

SQL Server Left Outer Join Multiple Tables Count

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

EXAMPLE:

SELECT COUNT(BOOKS.BOOK_NAME) AS BOOKS_PERAUTHOR,
AUTHORS.AUTHOR_FULLNAME,
BOOK_CATEGORY.CATEGORY_NAME
FROM BOOKS
LEFT OUTER JOIN AUTHORS
ON BOOKS.AUTHOR_ID=AUTHORS.AUTHOR_ID
LEFT OUTER JOIN BOOK_CATEGORY
ON BOOKS.BOOK_ID=BOOK_CATEGORY.BOOK_ID
GROUP BY AUTHORS.AUTHOR_FULLNAME,
BOOK_CATEGORY.CATEGORY_NAME;

With the help of the LEFT OUTER JOIN in the SELECT statement, we counted the book’s name based on the author’s name and category’s name from these tables i.e., BOOKS, AUTHORS, and BOOK_CATEGORY.

We hope you understand the use of the SQL Server COUNT function with the LEFT OUTER JOIN clause on multiple tables by the query.

Read: SQL Server Select a Join Without Join

SQL Server Left Outer Join Multiple Tables Distinct

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

EXAMPLE:

SELECT DISTINCT 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
WHERE BOOKS.BOOK_NAME IS NOT NULL 
AND BOOK_CATEGORY.CATEGORY_NAME IS NOT NULL;

The WHERE clause is used with the LEFT OUTER JOIN query example to retrieve unique authors’ names along with the names of their books and categories from AUTHORS, BOOKS, and BOOK_CATEGORY tables.

In the WHERE condition, we filtered the record of the book’s name and category’s name which doesn’t carry a NOT NULL value. This will retrieve records of the author’s name in the resultset.

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

Read: How To Update Table Using JOIN in SQL Server

SQL Server Left Outer Join Multiple Tables Having Joins

Let’s see an example of SQL Server LEFT OUTER JOIN with the HAVING clauses on multiple tables by the query.

SELECT COUNT(BOOKS.BOOK_NAME) AS BOOKS_PERAUTHOR,
AUTHORS.AUTHOR_FULLNAME,
BOOK_CATEGORY.CATEGORY_NAME
FROM BOOKS
LEFT OUTER JOIN AUTHORS
ON BOOKS.AUTHOR_ID=AUTHORS.AUTHOR_ID
LEFT OUTER JOIN BOOK_CATEGORY
ON BOOKS.BOOK_ID=BOOK_CATEGORY.BOOK_ID
GROUP BY AUTHORS.AUTHOR_FULLNAME,
BOOK_CATEGORY.CATEGORY_NAME
HAVING COUNT(BOOKS.BOOK_NAME)>1;

In the LEFT OUTER JOIN query example, we counted the book’s name based on the author’s name and category’s name from these tables i.e.; BOOKS, AUTHORS, and BOOK_CATEGORY by the HAVING clause.

We filtered the count of book names whose value is greater than 1 so that we can retrieve the author’s name with their category’s name in the resultset.

We hope you understood the use of SQL Server LEFT OUTER JOIN with the HAVING clause on multiple tables by the query.

Read: How to use Distinct in JOIN in SQL Server

SQL Server Left Outer Join Multiple Tables Like

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

EXAMPLE:

SELECT AUTHORS.AUTHOR_FULLNAME,
BOOKS.BOOK_NAME,
BOOK_CATEGORY.CATEGORY_NAME
FROM AUTHORS
LEFT OUTER JOIN BOOKS
ON AUTHORS.AUTHOR_ID=BOOKS.BOOK_ID
LEFT OUTER JOIN BOOK_CATEGORY
ON BOOKS.BOOK_ID=BOOK_CATEGORY.BOOK_ID
WHERE AUTHORS.ADDRESS LIKE '%279 Oakridge Avenue';

Here we see in the above query example, we retrieve records of the author’s name with their book’s name and its category from these tables i.e., AUTHORS, BOOKS, and BOOK_CATEGORY by the WHERE condition.

In the WHERE condition, we filter the author’s starting address by “279 Oakridge Avenue” so that we can get the author’s name their book’s name, and category name in the resultset.

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

Read: SQL Server Count Join Group By

SQL Server Left Outer Join Multiple Tables Row_Count

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

EXAMPLE:

SELECT ROW_NUMBER()
OVER(PARTITION BY BOOKS.BOOK_NAME
ORDER BY BOOKS.BOOK_EDITION) AS BOOKS_ROWNUMBER,
BOOKS.BOOK_NAME,
BOOKS.BOOK_EDITION,
AUTHORS.AUTHOR_FULLNAME
FROM BOOKS
LEFT OUTER JOIN AUTHORS
ON BOOKS.AUTHOR_ID=AUTHORS.AUTHOR_ID;

We numbered the records based on the book’s title and then arranged the records in ascending order based on the book’s edition using the LEFT OUTER JOIN in the SELECT statement.

Then, using the BOOKS and AUTHORS tables, we also obtain all records containing the name of the book, its edition, and the author’s name.

We hope you understand the use of SQL Server ROW_NUMBER function with the LEFT OUTER JOIN on multiple tables by the query.

Also, take a look at some more SQL Server tutorials.

• SQL Server SELF JOIN 
• SQL Server OUTER JOIN
• SQL Server Inner Join Top 1

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

• Working of SQL Server Left Outer Join Multiple Tables
• Use of SQL Server Left Outer Join Multiple Tables And Where Clause
• How to use SQL Server Left Outer Join Multiple Tables Between
• How to use SQL Server Left Outer Join Multiple Tables Count
• Use of SQL Server Left Outer Join Multiple Tables Distinct
• How to use SQL Server Left Outer Join Multiple Tables Group By
• How to use SQL Server Left Outer Join Multiple Tables Having Joins
• How to use SQL Server Left Outer Join Multiple Tables Like
• How to use SQL Server Left Outer Join Multiple Tables Row Count