Just in the context of this question, I want to post the 2 'APPLY' operators as well:

JOINS:

1. INNER JOIN = JOIN

2. OUTER JOIN

• LEFT OUTER JOIN = LEFT JOIN

• RIGHT OUTER JOIN = RIGHT JOIN

• FULL OUTER JOIN = FULL JOIN

3. CROSS JOIN

SELF-JOIN: This is not exactly a separate type of join. This is basically joining a table to itself using one of the above joins. But I felt it is worth mentioning in the context JOIN discussions as you will hear this term from many in the SQL Developer community.

APPLY:

1. CROSS APPLY -- Similar to INNER JOIN (But has added advantage of being able to compute something in the Right table for each row of the Left table and would return only the matching rows)
2. OUTER APPLY -- Similar to LEFT OUTER JOIN (But has added advantage of being able to compute something in the Right table for each row of the Left table and would return all the rows from the Left table irrespective of a match on the Right table)

https://www.mssqltips.com/sqlservertip/1958/sql-server-cross-apply-and-outer-apply/

https://sqlhints.com/2016/10/23/outer-apply-in-sql-server/

Real life example, when to use OUTER / CROSS APPLY in SQL

I find APPLY operator very beneficial as they give better performance than having to do the same computation in a subquery. They are also replacement of many Analytical functions in older versions of SQL Server. That is why I believe that after being comfortable with JOINS, one SQL developer should try to learn the APPLY operators next.

JOINs:

1. INNER JOIN = JOIN

2. OUTER JOIN

   • LEFT OUTER JOIN = LEFT JOIN
   • RIGHT OUTER JOIN = RIGHT JOIN
   • FULL OUTER JOIN = FULL JOIN

3. CROSS JOIN

Self-JOIN: This is not exactly a separate type of join. This is joining a table to itself using one of the above joins. You will hear this term from many in the SQL Developer community.

There are two APPLY operators:

1. CROSS APPLY: Similar to INNER JOIN (But has the added advantage of being able to compute something in the right table for each row of the left table and returns only the matching rows.)
2. OUTER APPLY: Similar to LEFT OUTER JOIN (But has the added advantage of being able to compute something in the right table for each row of the left table and returns all the rows from the left table irrespective of a match on the right table.)

https://www.mssqltips.com/sqlservertip/1958/sql-server-cross-apply-and-outer-apply/

https://sqlhints.com/2016/10/23/outer-apply-in-sql-server/

Real life example, when to use OUTER / CROSS APPLY in SQL

The APPLY operators are very beneficial as they give better performance than doing the same computation in a subquery. They are also a replacement of many analytical functions in older versions of SQL Server. So after being comfortable with JOINS a SQL developer should learn the APPLY operators.