SQL Server OUTER JOIN + Examples

In the SQL Server tutorial, we will learn how to use SQL Server OUTER JOIN clause on both tables by the query. The following is a list of the subtopics that will be covered:

• What is SQL Server OUTER JOIN?
• What is the difference between SQL Server OUTER JOIN and SQL Server INNER JOIN?
• How to use OUTER JOIN with WHERE clause in SQL Server?
• What is the difference between OUTER JOIN vs FULL OUTER JOIN in SQL Server?
• What will happen if we use OUTER JOIN without the ON condition in SQL Server?

SQL Server Outer Join

In SQL Server, the FULL OUTER JOIN clause returns a result set that includes rows from the right and left tables.

The columns of the right table will contain NULL if there are no matching rows for the row in the left table. The left table’s column will contain NULL if there are no matching rows for the row in the right table. Here is the image of the FULL OUTER JOIN in SQL Server:

Next, we created two tables named DRUG_COMPANY and COMPANY_OWNER in the database:

CREATE TABLE DRUG_COMPANY (
	COMPANY_ID INT,
	COMPANY_NAME VARCHAR(50)
);
INSERT INTO DRUG_COMPANY (COMPANY_ID, COMPANY_NAME) VALUES
(1, 'Virtus Pharmaceuticals'),
(2, 'Dispensing Solutions, Inc.'),
(3, 'L''Oreal USA Products Inc'),
(4, 'Dolgencorp, Inc. (DOLLAR GENERAL & REXALL)'),
(5, 'Rebel Distributors Corp'),
(6, 'Preferred Pharmaceuticals, Inc.'),
(7, 'Nelco Laboratories, Inc.'),
(8, 'GFS US LLC'),
(9, 'Topco Associates LLC'),
(10, 'Meijer Distribution Inc');

CREATE TABLE COMPANY_OWNER(
    ID INT PRIMARY KEY ,
    OWNER_NAME VARCHAR(120) NOT NULL,
    COMPANY_ID INT,
    FOREIGN KEY (COMPANY_ID) 
        REFERENCES COMPANY_OWNER(ID)
);

INSERT INTO COMPANY_OWNER (ID, OWNER_NAME, COMPANY_ID ) VALUES
(1, 'Katheryn Gaynsford',5),
(2, 'Shell Jancey',6),
(3, 'Hyacinth Halsted',7),
(4, 'Mathe Milne',1),
(5, 'Madge Pears',9),
(6, 'Welsh Bilbery',2),
(7, 'Cate Cubuzzi',3),
(8, 'Corny Urrey',4),
(9, 'Yolanthe De Blase',10),
(10, 'Petronilla Vondrak',8);

Here is the image of the DRUG_COMPANY and COMPANY_OWNER tables using the SELECT statement.

SYNTAX:

SELECT SELECTED_COLUMN 
FROM TABLE_1
FULL OUTER JOIN TABLE_2 
ON TABLE_1.COMMON_COLUMN=TABLE_2.COMMON_COLUMN'

In the syntax explanation:

• First, we need to specify TABLE_1 and TABLE_2 respectively.
• Second, we need to specify the right table (TABLE_2) with join prediction on the common column of both tables for the result set.

EXAMPLE:

SELECT DRUG_COMPANY.COMPANY_NAME, COMPANY_OWNER.OWNER_NAME
FROM DRUG_COMPANY
FULL OUTER JOIN COMPANY_OWNER
ON DRUG_COMPANY.COMPANY_ID=COMPANY_OWNER.COMPANY_ID;

With the help of the FULL OUTER JOIN example, the query returns the company name with the owner name from both tables for the result set. The FULL OUTER JOIN query data from DRUG_COMPANY and COMPANY_OWNER tables.

We hope that you have understood how to use FULL OUTER JOIN in SQL Server respectively and also used an example to make you understand.

Read: RIGHT JOIN in SQL Server

SQL Server Outer Join Vs Inner Join

We need to understand what is difference between OUTER JOIN and INNER JOIN in SQL Server. We already explained the definition with an example of SQL Server OUTER JOIN in the above subtopic “SQL Server OUTER JOIN”.

Let’s understand what the use of INNER JOIN is in SQL Server.

SQL Server INNER JOIN

One of the most often used joins in SQL Server is the INNER JOIN. It allows us to query data from two or more related tables. The following table will retrieve information on owner_name from the COMPANY_OWNER table:

SELECT ID,OWNER_NAME,COMPANY_ID 
FROM COMPANY_OWNER
ORDER BY OWNER_NAME DESC;

The above query returns information about the owner’s name but not the company names from the table. To include the COMPANY_NAME column’s record in the result set, we have to use the INNER JOIN clause by the following query:

SELECT COMPANY_OWNER.OWNER_NAME,DRUG_COMPANY.COMPANY_NAME,
COMPANY_OWNER.COMPANY_ID
FROM COMPANY_OWNER 
INNER JOIN DRUG_COMPANY 
ON COMPANY_OWNER.COMPANY_ID=DRUG_COMPANY.COMPANY_ID
ORDER BY COMPANY_OWNER.OWNER_NAME DESC;

As can be seen in the result set, the SELECT collects all records containing the owner name and company name from both tables, i.e., (DRUG COMPANY), which act as a left table, using the INNER JOIN clause. Also acting as a right table is the COMPANY_OWNER table.

In the end, we have also arranged the records of the OWNER_NAME column in descending order for the result set.

We hope that you have understood the difference between SQL Server OUTER JOIN and INNER JOIN by an example query.

Read: SQL Server Outer Join With Count

SQL Server Outer Join With Where Clause

We hope that you know what is the use of the WHERE clause in SQL Server. Let me give a short definition of the WHERE clause.

The table’s records are filtered using the SQL Server WHERE clause. It will only extract the records that meet a predetermined requirement. It can also be used on its own without the SELECT command. And it also holds for commands like DELETE and UPDATE.

EXAMPLE:

SELECT DRUG_COMPANY.COMPANY_NAME, COMPANY_OWNER.OWNER_NAME
FROM DRUG_COMPANY
FULL OUTER JOIN COMPANY_OWNER
ON DRUG_COMPANY.COMPANY_ID=COMPANY_OWNER.COMPANY_ID
WHERE COMPANY_NAME LIKE '%c';

All firm names from the COMPANY_NAME column whose names end in the letter c were provided, as is seen in the output, along with the owner name.

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

Read: Recursive Trigger in SQL Server

SQL Server Outer Join vs Full Outer Join

There is a small difference between the OUTER JOIN and FULL OUTER JOIN clauses in SQL Server and we have to use the FULL keyword in the OUTER JOIN clause. If we try to use only the OUTER JOIN in SQL Server it will throw an error. Here is an example below of both clauses.

OUTER JOIN Example:

SELECT DRUG_COMPANY.COMPANY_NAME, COMPANY_OWNER.OWNER_NAME
FROM DRUG_COMPANY
OUTER JOIN COMPANY_OWNER
ON DRUG_COMPANY.COMPANY_ID=COMPANY_OWNER.COMPANY_ID;

The above query will return a syntax error because it doesn’t contain the FULL OUTER JOIN clause in the query.

FULL OUTER JOIN Example:

SELECT DRUG_COMPANY.COMPANY_NAME, COMPANY_OWNER.OWNER_NAME
FROM DRUG_COMPANY
FULL OUTER JOIN COMPANY_OWNER
ON DRUG_COMPANY.COMPANY_ID=COMPANY_OWNER.COMPANY_ID;

With the help of FULL OUTER JOIN, the SELECT statement retrieves all records of the left_table (DRUG_COMPANY) with matching records from the right_table (COMPANY_OWNER) table for the result set.

We hope that you have understood the difference between the OUTER JOIN and the FULL OUTER JOIN in SQL Server. For your understanding, we have used two different examples.

Read: How to Debug an SQL Server Trigger

SQL Server Outer Join Without On

If we use the OUTER JOIN in SQL Server without the ON condition, it will throw a syntax error or logical error as per query requirement. The ON condition is a necessary part of the OUTER JOIN for tables to get the result set.

Here is an example of an OUTER JOIN in an SQL Server that doesn’t have an ON condition:

SELECT COMPANY_OWNER.OWNER_NAME,DRUG_COMPANY.COMPANY_NAME
FROM COMPANY_OWNER
FULL OUTER JOIN DRUG_COMPANY;

As we see in the above query result set, it provides an incorrect syntax error near ‘;’. This means that the ON condition is not there in the FULL OUTER JOIN clause in SQL Server.

SQL Server OUTER JOIN with ON condition example:

SELECT DRUG_COMPANY.COMPANY_NAME, COMPANY_OWNER.OWNER_NAME
FROM DRUG_COMPANY
FULL OUTER JOIN COMPANY_OWNER
ON DRUG_COMPANY.COMPANY_ID=COMPANY_OWNER.COMPANY_ID;

We believe you now understand why SQL Server needs an OUTER JOIN with the ON condition. Two instances used with both with and without the ON condition were used.

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

• Execute Trigger in SQL Server
• SQL Server SELF JOIN
• SQL Server INNER JOIN Tutorial
• SQL Server Trigger Before Update
• SQL Server Trigger Before Insert
• SQL Server Left Join With Count 
• SQL Server Select a Join Without Join
• Disable Trigger in SQL Server

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

• What is SQL Server OUTER JOIN?
• What is the difference between SQL Server OUTER JOIN and SQL Server INNER JOIN?
• How to use OUTER JOIN with WHERE clause in SQL Server?
• What is the difference between OUTER JOIN vs FULL OUTER JOIN in SQL Server?
• What will happen if we use OUTER JOIN without the ON condition in SQL Server?