why ON clause used on full outer join?

Question

As per my understanding, FULL OUTER JOIN is a combination of LEFT OUTER JOIN and RIGHT OUTER JOIN. In this case, it simply joins two tables with all entries. Please let me know why we are giving "ON" clause for FULL OUTER JOIN ? or explain how the ON clause is applied or what difference it makes in the query?

Eg Query:

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders
ON Customers.CustomerID=Orders.CustomerID

It defines the criteria that is used when determining that a row from either side matches. Same as for other types of join. Why the confusion? Are you thinking of cross join? — Martin Smith, Jun 17 '14 at 07:24
[see the doc here](http://technet.microsoft.com/en-us/library/ms191517(v=sql.105).aspx) `ON is used in all join types except a Cross join` — vhadalgi, Jun 17 '14 at 07:24

score 8 · Answer 1 · answered Jun 17 '14 at 07:31

8

This diagram will help you to understand joins.

Diagram

answered Jun 17 '14 at 07:31

Rapunzo

966
5
21
42

+1 Diagram is perfect,but it doesn't fullfil the answer no negative anyway. – sunleo Jul 29 '14 at 08:05

score 3 · Accepted Answer · edited May 23 '17 at 12:25

A cross join produces a Cartesian product between the two tables, returning all possible combinations of all rows. It has no on clause because you're just joining everything to everything.

A full outer join is a combination of a left outer and right outer join. It returns all rows in both tables that match the query's where clause, and in cases where the on condition can't be satisfied for those rows it puts null values in for the un-populated fields.

From @Donnie answer

score 1 · Answer 3 · answered Jun 17 '14 at 07:25

ON is the join condition and will be used on all join types except cross join

Full outer join is similar to this:

SELECT employee.LastName, employee.DepartmentID,
       department.DepartmentName, department.DepartmentID
FROM employee
INNER JOIN department ON employee.DepartmentID = department.DepartmentID

UNION ALL

SELECT employee.LastName, employee.DepartmentID,
       CAST(NULL AS VARCHAR(20)), CAST(NULL AS INTEGER)
FROM employee
WHERE NOT EXISTS (
    SELECT * FROM department
             WHERE employee.DepartmentID = department.DepartmentID)

UNION ALL

SELECT CAST(NULL AS VARCHAR(20)), CAST(NULL AS INTEGER),
       department.DepartmentName, department.DepartmentID
FROM department
WHERE NOT EXISTS (
    SELECT * FROM employee
             WHERE employee.DepartmentID = department.DepartmentID)

Test data:

CREATE TABLE department
(
 DepartmentID INT,
 DepartmentName VARCHAR(20)
);

CREATE TABLE employee
(
 LastName VARCHAR(20),
 DepartmentID INT
);

INSERT INTO department VALUES(31, 'Sales');
INSERT INTO department VALUES(33, 'Engineering');
INSERT INTO department VALUES(34, 'Clerical');
INSERT INTO department VALUES(35, 'Marketing');

INSERT INTO employee VALUES('Rafferty', 31);
INSERT INTO employee VALUES('Jones', 33);
INSERT INTO employee VALUES('Heisenberg', 33);
INSERT INTO employee VALUES('Robinson', 34);
INSERT INTO employee VALUES('Smith', 34);
INSERT INTO employee VALUES('John', NULL);

Reference

why ON clause used on full outer join?

3 Answers3