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.BWHERE 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 FULLOUTER JOIN B ON A.A = B.BWHERE 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":

SELECT * FROM A CROSS JOIN BWHERE 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:

SELECTA1.A AS A1, A2.AAS A2 FROM A AS A1LEFT 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: