The different types of JOINs in Microsoft SQL Server – INNER, LEFT, RIGHT, FULL and CROSS #shorts

Let’s explore in 60 seconds the various types of JOINs in SQL.
My SQL Server Udemy courses are:
70-461, 70-761 Querying Microsoft SQL Server with T-SQL: https://rebrand.ly/querying-microsoft-sql-server
98-364: Database Fundamentals (Microsoft SQL Server): https://rebrand.ly/database-fundamentals
70-462 SQL Server Database Administration (DBA): https://rebrand.ly/sql-server-dba
Microsoft SQL Server Reporting Services (SSRS): https://rebrand.ly/sql-server-ssrs
SQL Server Integration Services (SSIS): https://rebrand.ly/sql-server-ssis
SQL Server Analysis Services (SSAS): https://rebrand.ly/sql-server-ssas-mdx
Microsoft Power Pivot (Excel) and SSAS (Tabular DAX model): https://rebrand.ly/microsoft-powerpivot-ssas-tabular-dax
—-
There are five different types of JOINS:
1. The INNER JOIN – this is the default JOIN. Only those rows in the two tables which match.
2. The LEFT JOIN – all of the left-hand table rows with only those in the right-hand table which matches.
3. The RIGHT JOIN – all of the right-hand table rows with those in the right-hand table which matches.
4. The FULL JOIN – all of the two table rows, taking account of matching.
5. The CROSS JOIN – all of the first table’s rows against all of the second table’s rows. This multiplies the two tables’ rows, has not account of matching, and therefore is not often used.
—-
The query I used is:
WITH table1 AS
(SELECT object_id, name AS ObjectName FROM sys.objects where object_ID between 3 and 19),
table2 AS
(SELECT object_id, column_id, name as ColumnName FROM sys.columns where object_ID between 10 and 29)
SELECT *
FROM table1 LEFT JOIN table2
ON table2.object_id = table1.object_id
WHERE table2.column_id = 1 OR table2.column_id IS NULL
ORDER BY ISNULL(table1.object_id, table2.object_id), table2.column_id