Table Relationship vs SQL Join

I thought that creating a relationship between two tables in Power Pivot would allow me to select a column from Table A and a column from Table B and then see the corresponding items related per row, like a SQL join. I don't understand the logic as to why it doesn't work that way but it certainly doesn't. So what I figured I would do is join all of these tables together into one query and then put it into Power Pivot. It works well but surely there is a better way because this query is massive and annoying to maintain.

Also, can anybody help me understand the logic of the table relationships? Are relationships just to have the ability to aggregate data?

I'm sorry about that Xlbiznes, I don't think I explained my question well enough. I know all of the SQL I need but I'm trying to get Excel to show me similar output. I guess my question should be: How can I get Excel to show me the same output as a SQL join? So if I have Table A and B in Power Pivot, I want to join those into one one pivot table.

Thanks romperstomper. What I'm finding is that for a pivot table in which there is no data field in the "Values" quadrant, a "joined" type of table is not possible. Drop a data field into the "Values" and of course it aggregates and gets rid of anything with no value. I guess I just don't see the logic that, without an aggregate, it will display every possible value from a column like in the picture attached.

I apologize for the simplistic understanding and basic questioning but I am very new to set/data manipulation and am obviously missing some key logic concept somewhere.

I should mention that the two tables are in the data model with a relation between ProductID
pivot.png

Well at least it makes sense now, thanks.