Left [Outer] Join and Right [Outer] Join

CREATE TABLE Customers ( CustomerId INT, Name VARCHAR(50) );
INSERT INTO Customers VALUES(1,'Ali'), (2,'Veli'), (3,'Selami');

CREATE TABLE Orders (OrderId INT, CustomerId INT);
INSERT INTO Orders VALUES(100,1), (200,4), (300,3);

SELECT * FROM Customers C LEFT OUTER JOIN Orders O ON O.CustomerId = C.CustomerId

SELECT * FROM Customers C LEFT JOIN Orders O ON O.CustomerId = C.CustomerId

LEFT OUTER JOIN / LEFT JOIN returns all the rows from the LEFT table and the corresponding matching rows from the right table. If right table doesn’t have the matching record then for such records right table column will have NULL value in the result.

SELECT * FROM Customers C RIGHT OUTER JOIN Orders O ON O.CustomerId = C.CustomerId;

SELECT * FROM Customers C RIGHT JOIN Orders O ON O.CustomerId = C.CustomerId;

RIGHT OUTER JOIN / RIGHT JOIN returns all the rows from the RIGHT table and the corresponding matching rows from the left table. If left table doesn’t have the matching record then for such records left table column will have NULL value in the result.

As per the documentation: FROM (Transact-SQL):

<join_type> ::= 
    [ { INNER | { { LEFT | RIGHT | FULL } [ OUTER ] } } [ <join_hint> ] ]
    JOIN

The keyword OUTER is marked as optional (enclosed in square brackets). In this specific case, whether you specify OUTER or not makes no difference. Note that while the other elements of the join clause is also marked as optional, leaving them out will make a difference.

For instance, the entire type-part of the JOIN clause is optional, in which case the default is INNER if you just specify JOIN. In other words, this is legal:

SELECT *
FROM A JOIN B ON A.X = B.Y

Here’s a list of equivalent syntaxes:

A LEFT JOIN B            A LEFT OUTER JOIN B
A RIGHT JOIN B           A RIGHT OUTER JOIN B
A FULL JOIN B            A FULL OUTER JOIN B
A INNER JOIN B           A JOIN B

1. INNER JOIN – fetches data if present in both the tables.
2. OUTER JOIN are of 3 types:
   1. LEFT [OUTER] JOIN – fetches data from both table if present in the left table.
   2. RIGHT [OUTER] JOIN – fetches data from both table if present in the right table.
   3. FULL [OUTER] JOIN – fetches data if present in either of the two tables.
3. CROSS JOIN joins everything to everything.