Hi, table 1 Table 2 select A.* from table1a LEFT join table2 b on a.col1=b.col1 output: which is correct but on right join it shows above results how to calculate this join and how it will react on NULL and blank

If the left join gives you the correct result, use a left join. A RIGHT JOIN B is the same B LEFT JOIN A. Personally, I never use RIGHT JOIN. It gives me a headache every time I see it. Also, keep in mind that when joining two tables, no matter the join type, the join column(s) should be a unique key in at least in one of the tables. If they are non-unique in both tables, you will get a row explosion. That is, if there are three rows in one table and four in the other, all with the same key value, the join will produce twelve rows.

left and right join - Microsoft Q&A

Accepted answer

Erland Sommarskog 112.7K Reputation points MVP

2024-06-23T11:56:27.08+00:00

If the left join gives you the correct result, use a left join.

A RIGHT JOIN B is the same B LEFT JOIN A. Personally, I never use RIGHT JOIN. It gives me a headache every time I see it.

Also, keep in mind that when joining two tables, no matter the join type, the join column(s) should be a unique key in at least in one of the tables. If they are non-unique in both tables, you will get a row explosion. That is, if there are three rows in one table and four in the other, all with the same key value, the join will produce twelve rows.
Please sign in to rate this answer.
Vineet S 1,070 Reputation points

2024-06-23T12:05:43.1733333+00:00

can you explain me the right join in above query

Erland Sommarskog 112.7K Reputation points MVP

2024-06-23T12:12:36.8933333+00:00

Didn't I say that I get a headache from right joins? :-)

For each row in the B table, find the matching rows in A. Display the matching row.

Table B has three rows with 1, 2 and NULL. The 1 matches three rows in A, so three 1s are displayed. 2 and NULL matches no rows in A, so for these you get NULL displayed in the column from A.

It somewhat easier to understand if you include columns from both tables in the output.

Vineet S 1,070 Reputation points

2024-06-23T13:22:10.34+00:00

How to check for right outer join with left table

Erland Sommarskog 112.7K Reputation points MVP

2024-06-23T15:26:24.38+00:00

How to check for right outer join with left table

Not sure I understand, but here is a simple rule: Never use right join!
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

left and right join

0 additional answers

Your answer