Seven SQL Server JOIN types.

Not sure if I can compete with Wikipedia, but I will try and touch that beginner's subject.

At first, will create two tables in our TestDB and fill them by values.

Use TestDB
GO
CREATE TABLE A(A INT PRIMARY KEY);
GO
CREATE TABLE B(B INT PRIMARY KEY);
GO
INSERT INTO A VALUES (1),(2),(3),(4),(5),(0);
GO
INSERT INTO B VALUES (9),(4),(5),(6),(7),(8);
GO
SELECT * FROM A;
GO
SELECT * FROM B;
GO

That code creates two tables A & B and fills them by values A: from 0 to 5 and B: from 4 to 9.

Now will start joining these tables:

1. INNER JOIN

SELECT * FROM A INNER JOIN B ON A.A = B.B;

That join extracts ONLY matching records from A&B:

2. LEFT JOIN 

SELECT * FROM A LEFT OUTER JOIN B ON A.A = B.B;

That Join extracts ALL records from the LEFT table and ONLY matching records from the right table within that join:

There is also variation of LEFT JOIN with exclusion of matching records. That join very useful when you need to extract only not-matching records:

SELECT * FROM A LEFT OUTER JOIN B ON A.A = B.B

WHERE B.B IS NULL;

3. RIGHT JOIN 

SELECT * FROM A RIGHT OUTER JOIN B ON A.A = B.B;

That Join extracts ALL records from the RIGHT table and ONLY matching records from the left table within that join:

There is also variation of RIGHT JOIN with exclusion of matching records. That join works similarly as a left exclusion join:

SELECT * FROM A RIGHT OUTER JOIN B ON A.A = B.B
WHERE A.A IS NULL;

Generally, RIGHT JOIN is the opposite of LEFT JOIN. Just for the sake of consistency, try to never use it in your code, there is nothing worse within a query to have a mix of LEFT and RIGHT joins

4. FULL OUTER JOIN

SELECT * FROM A FULL OUTER JOIN B ON A.A = B.B;

That Join includes ALL values from both tables regardless matching records:

There is also variation of FULL JOIN with exclusion of matching records. It just simply excludes all matching records from the output:

SELECT * FROM A FULL OUTER JOIN B ON A.A = B.B
WHERE A.A IS NULL OR B.B IS NULL;

5. CROSS JOIN

SELECT * FROM A CROSS JOIN B;

That special join returns Cartesian product of two tables - result of multiplication of all rows in one table by all rows in another. Cross join can be very dangerous when you link huge tables.
In our case, because we have only six records in each table, we will have resulting data set of 36 records (6x6=36). Each record in table "A" will be linked to each record in table "B":

For CROSS JOIN we also can specify an exclusion of matching records:

SELECT * FROM A CROSS JOIN B
WHERE A.A != B.B;
That query will produce 34 records (36 - 2 matching records)

If we try to select only matching records like this:

SELECT * FROM A CROSS JOIN B
WHERE A.A != B.B;
The result will be similar to INNER JOIN.

We also can get Cartesian product by using INNER JOIN:

SELECT * FROM A INNER JOIN B ON 0 = 0;
That query is identical to regular CROSS JOIN and will return 36 records.

6. Self Join 

Yes, you can do it:
SELECT * FROM A AS A1 INNER JOIN A AS A2 ON  A1.A=A2.A;

Which will return All rows from a table doubling columns. Usually, you do not need that, but there are special cases like linking to the next or previous row:

SELECT A1.A AS A1, A2.A AS A2 FROM A AS A1
LEFT OUTER JOIN A AS A2 ON  A1.A + 1 = A2.A;

There can be multiple Self Join scenarios, that is just one of them.
 

7. APPLY

That is the most complicated type of join and it is usually used with function calls.
There are two variations of APPLY: CROSS and OUTER:
- OUTER APPLY returns ALL Rows from the left side of the query (like LEFT JOIN);
- CROSS APPLY works as regular JOIN and returns only "matching" rows, which are not enpty.

So, will create an artificial function just for the sake of an example:

CREATE FUNCTION dbo.fn_C (@C INT) RETURNS TABLE AS
RETURN (
    SELECT TOP (CASE WHEN @C % 2 = 0 THEN 0 ELSE 1 END) 
        CAST(RIGHT(CAST(@C * PI() AS VARCHAR),3) AS INT) AS C
);

Here is a simple example of using OUTER APPLY with that function:

SELECT * FROM A OUTER APPLY dbo.fn_C (A) AS C;

OUTER JOIN Selects all values from table "A" and all corresponding values from the Function.

Here is a simple example of using OUTER APPLY with that function:

SELECT * FROM A CROSS APPLY dbo.fn_C (A) AS C;

CROSS APPLY extracted from the table "A" only those records, which had correspondence in the function.

The biggest advantage APPLY gives you when you need to use value from one table within a sub-query with a second table:

SELECT * FROM A OUTER APPLY 
(SELECT MAX(B) FROM B WHERE A.A * 2 > B.B ) AS C(M);

The result will look like this:

Conclusion

There can be more than two table join with any crazy combination of different types of joins. Knowing how each join works is important to return right data set.