JOINS are an essential concept in SQL Server that allow you to combine data from multiple tables based on a specified condition. In this article, we will explore the different types of JOINS and their usage.

INNER JOIN

An INNER JOIN returns rows when there is at least one match in both the tables. It combines the matching rows from both tables based on the specified condition.

OUTER JOIN

There are three different types of OUTER JOIN methods:

LEFT OUTER JOIN

A LEFT OUTER JOIN returns all the rows from the left table in conjunction with the matching rows from the right table. If there are no columns matching in the right table, it returns NULL values.

RIGHT OUTER JOIN

A RIGHT OUTER JOIN returns all the rows from the right table in conjunction with the matching rows from the left table. If there are no columns matching in the left table, it returns NULL values.

FULL OUTER JOIN

A FULL OUTER JOIN combines the results of both LEFT and RIGHT OUTER JOIN. It returns rows from either table when the conditions are met and returns NULL values when there is no match.

CROSS JOIN

A CROSS JOIN is a Cartesian join that does not require any condition to join. It returns a result set that contains records that are a multiplication of the record number from both tables.

Additional Notes related to JOIN

Here are three classic examples where OUTER JOIN is useful:

Example 1:

SELECT t1.*
FROM Table1 t1
WHERE t1.ID NOT IN (
    SELECT t2.ID
    FROM Table2 t2
)

The above query can be easily replaced by an OUTER JOIN, which is considered a best practice:

SELECT t1.*, t2.*
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t2.ID IS NULL

Example 2:

SELECT t1.*, t2.*
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t2.ID IS NULL

The above example can also be created using a RIGHT OUTER JOIN.

Remember, the term “Not Inner Join” does not exist in database terminology. However, when a FULL OUTER JOIN is used along with a WHERE condition, as explained in the above examples, it will give you exclusive results to an Inner Join. This join will give all the results that were not present in the Inner Join.

Understanding JOINS is crucial for working with SQL Server and querying data from multiple tables efficiently. By mastering the different types of JOINS, you can enhance your SQL skills and improve your ability to retrieve and manipulate data.

Feel free to download the complete SQL script here.

I hope this article has provided you with a clear understanding of SQL Server JOINS. If you have any feedback or suggestions for future articles, please let me know in the comments below.