Right Join vs where a value exists in another table

Question

Without realizing it I've switched to the first block of code as a preference. I am curious if it is a best practice or more efficient to use the first block of code over the second or vice versa?

In my opinion the first is more readable and concise since all the columns are from one table.

SELECT Column2, Column3, Column4
FROM Table1
WHERE Column1 in (SELECT Column1 FROM Table2)

vs

SELECT A.Column2, A.Column3, A.Column4
FROM Table1 A
RIGHT JOIN Table2 B ON A.Column1 = B.Column1

Just hoping for clarification on best practices/efficiency of each statement and if there's an accepted form.

Answer 1

Your two queries don't do the same thing.

Your first one

SELECT Column2, Column3, Column4
FROM Table1
WHERE Column1 in (SELECT Column1 FROM Table2)

is called a semi-join. It works like an inner join where the resultset has no columns from the second table. This is another way of writing the semi-join, but you have pointed out that your way is easier for you to read and reason about. (I agree.) Modern query planners satisfy either way of writing the semi-join the same way. This is the other way of writing the semi-join.

SELECT Table1.Column2, Table1.Column3, Table1.Column4
 FROM Table1
INNER JOIN Table2 ON Table1.Column1 = Table2.Column1 

Your second query is this. (By the way, RIGHT JOINs are far less common than LEFT JOINs in production code; many people have to stop and think twice when reading a RIGHT JOIN.)

SELECT A.Column2, A.Column3, A.Column4
FROM Table1 A
RIGHT JOIN Table2 B ON A.Column1 = B.Column1

This will produce resultset rows for every row in Table2 whether or not they match rows in Table1. Inner joins only deliver the rows that match the ON condition for both joined tables, and that's what you want.

Left joins produce at least one row for every row in Table1, even if it doesn't match. It's the same mutatis mutandis for right joins.