sql server - Select Name instead OF ID in table with ID-Ref Column SQL

Question

Lets say we have 2 Tables:

 Table A        Table B
 - A_ID         - B_ID
 - A_Name       - A_ID

I need a select statement, that selects * from Table B showing the A_NAME instead of the A_ID.

By trying it I got the following select statement which ... doesn't work to well. It is giving me a lot of nulls, but no names.

SELECT B_ID, 
       (select A_NAME from TableA as A where A.A_ID = B.A_ID) as Name
FROM TableB as B

---

Thanks for all your Answers.

The final solution:

The shown query DOES work (even though it may be slow) and the solutions in the answers also do work.

The problem why it didn't give results for me was because of my data. On another database with the same schema all the commands work.

Answer 1

You should try LEFT JOIN

SELECT 
 B_ID, A_Name 
FROM 
 tableB B LEFT JOIN tableA A
   ON B.A_ID = A.A_ID