Understanding OUTER JOIN in sql server

 
OUTER JOIN  :
    In comparison to an inner join, an outer join displays the result set containing all the rows from one table and the matching row from another table.
       Other words, Outer join extends the functionality of inner join. It returns following rows:
•    the same rows as inner join i.e. rows from both tables, which matches join condition and
•    rows from one or both tables, which do not match join condition along with NULL values in place of other table's columns.

Outer join Syntax is below.
     Select[column list]
from[ left joined table]                                       
Left! Right! Full [OUTER]  join  [right joined table]           
   on [join condition]

 NOTE: - Only one of the keywords left, right, full can be provided but exactly one is required. Keyword OUTER sometimes is avoided, but anyway keywords left, right or full indicate it is outer join. After the keyword ON join condition is written, generally it can contain many predicates connected with Boolean AND, OR, NOT. 


 Left outer join. 

A left outer join returns all rows from the table specified on the left side of the LEFT OUTER JOIN keyword and the matching rows from the table specified on the right side. The rows in the table specified on the left side for which matching rows are not found in table specified on the right side, NULL values are displayed in the columns that get data from the table specified on the right side.
                  Other words, Left outer join will output all rows from left input sets based on specified join predicate, even though rows from left input sets doesn’t necessarily have its match at right input sets.

Query of LEFT OUTER JOIN

CREATE TABLE customer

(custid CHAR(3) NOT NULL PRIMARY KEY,
firstname VARCHAR(10) NOT NULL);

INSERT INTO customer (custid, firstname) VALUES ('ant','anton');
INSERT INTO customer (custid, firstname) VALUES ('rng','rangga');
INSERT INTO customer (custid, firstname) VALUES ('joh','johan');
INSERT INTO customer (custid, firstname) VALUES ('Har','Harry');

CREATE TABLE sales

(salesID INT NOT NULL PRIMARY KEY,
custid CHAR(3) NULL,
qty    INT);

INSERT INTO sales (salesID, custid, qty) VALUES (1,'ant',10);
INSERT INTO sales (salesID, custid, qty) VALUES (2,'rng',20);
INSERT INTO sales (salesID, custid, qty) VALUES (3,'rng',25);
INSERT INTO sales (salesID, custid, qty) VALUES (4,'smi',10);

command in LEFT OUTER JOIN
 SELECT c.custid, c.firstname, s.salesID, s.custid, qty
FROM customer AS c
LEFT OUTER JOIN sales AS s
ON c.custid = s.custid


 Right outer join.

          A right outer join returns all the rows from the table specified on the right side of the RIGHT OUTER JOIN keyword and the matching rows from the table specified on the left side.
               A right outer join is the reverse of a left outer join. All rows from the right table are returned. Null values are returned for the left table any time a right table row has no matching row in the left table.

Query of RIGHT OUTER JOINCREATE TABLE customer

(custid CHAR(3) NOT NULL PRIMARY KEY,
 firstname VARCHAR(10) NOT NULL);

INSERT INTO customer (custid, firstname) VALUES ('ant','anton');
INSERT INTO customer (custid, firstname) VALUES ('rng','rangga');
INSERT INTO customer (custid, firstname) VALUES ('joh','johan');
INSERT INTO customer (custid, firstname) VALUES ('Har','Harry');

CREATE TABLE sales

(salesID INT NOT NULL PRIMARY KEY,
custid CHAR(3) NULL,
qty    INT);

INSERT INTO sales (salesID, custid, qty) VALUES (1,'ant',10);
INSERT INTO sales (salesID, custid, qty) VALUES (2,'rng',20);
INSERT INTO sales (salesID, custid, qty) VALUES (3,'rng',25);
INSERT INTO sales (salesID, custid, qty) VALUES (4,'smi',10);


command in RIGHT OUTER JOIN
SELECT c.custid, c.firstname, s.salesID, s.custid, qty
FROM customer AS c
RIGHT OUTER JOIN sales AS s
ON c.custid = s.custid


 Full Outer Join.

A full outer join is a combination of left outer join and right outer join. This join returns all the matching and non-matching row from both the tables. However, the matching records are displayed only once. In case of non-matching rows, a NULL value is displayed for the columns for which data is not available.
             A full outer join returns all rows in both the left and right tables. Any time a row has no match in the other table, the select list columns from the other table contain null values. When there is a match between the tables, the entire result set row contains data values from the base tables.

Query of FULLOUTER JOINCREATE TABLE customer

(custid CHAR(3) NOT NULL PRIMARY KEY,
 firstname VARCHAR(10) NOT NULL);

INSERT INTO customer (custid, firstname) VALUES ('ant','anton');
INSERT INTO customer (custid, firstname) VALUES ('rng','rangga');
INSERT INTO customer (custid, firstname) VALUES ('joh','johan');
INSERT INTO customer (custid, firstname) VALUES ('Har','Harry');

CREATE TABLE sales

(salesID INT NOT NULL PRIMARY KEY,
custid CHAR(3) NULL,
qty    INT);

INSERT INTO sales (salesID, custid, qty) VALUES (1,'ant',10);
INSERT INTO sales (salesID, custid, qty) VALUES (2,'rng',20);
INSERT INTO sales (salesID, custid, qty) VALUES (3,'rng',25);
INSERT INTO sales (salesID, custid, qty) VALUES (4,'smi',10);


command in FULL OUTER JOIN
SELECT c.custid, c.firstname, s.salesID, s.custid, qty
FROM customer AS c
Full OUTER JOIN sales AS s
ON c.custid = s.custid