Join and get data conditionally

If I have the table following structure in SQL Server Db

Table AA

Table BB

And a Stored Procedure

CREATE PROCEDURE GetData
(
    @ColumnA1 INT,
)
AS
BEGIN
    SELECT  TOP 1   A.ColumnA1, A.ColumnA2, A.ColumnA3, B.ColumnB1, B.ColumnB2
    FROM        AA as A
    INNER JOIN  BB as B on A.ColumnA3 = B.ColumnB1
    WHERE       A.ColumnA1 = @ColumnA1
END
GO

But there could be times when AA.ColumnA3 will not have the value and will be null. In that case, I do not want to do the join and get data from BB. Instead, I want to return null for B.ColumnB1 and B.ColumnB2.

How can I achieve this?

>Solution :

From what I understand, you want to use a LEFT JOIN.

There are different join types and in the snippet you showed you used an INNER JOIN. An INNER JOIN only joins the rows that have a matching record in the both tables.

If you want to get all the rows from the left, aka your base table you select from and join matching rows from the right or display null if there is none, use the LEFT JOIN.

If you want to do the opposite of this, just use the RIGHT JOIN.

If you need more context on how joins work, check this out:
https://www.w3schools.com/sql/sql_join.asp

So your snipped would looks something like this:

CREATE PROCEDURE GetData
(
    @ColumnA1 INT,
)
AS
BEGIN
    SELECT  TOP 1   A.ColumnA1, A.ColumnA2, A.ColumnA3, B.ColumnB1, B.ColumnB2
    FROM        AA as A

    -- I changed the JOIN type here
    LEFT JOIN  BB as B on A.ColumnA3 = B.ColumnB1
    WHERE       A.ColumnA1 = @ColumnA1
END
GO