The 3 joins in a table/query

[bearcub]

I thought that I understand the differences between the 3 different.

Join 1: Only include rows where the joined fields from both tables are equal.
Question: Does this mean that it is only going to display the records where the join field in both tables are equal? 
(is this an inner or outer join?)

Join 2: Include all records from the table A and only those records from table B where the joined fields are equal. Is this a left outer join?

Join 3 - just the reverse of Join 2 (is this a right outer join).

I'm looking at the wording and it seems to read the same to me though I know it isn't. I must be having a senior moment or two.

Thank you for the clarification in advance

Michael

 

[xenou]

I think you have it right.

1 is an inner join
2 is a left (outer) join
3 is a right (outer) join

I personally never use right join.


Edit:
note that by not using right join, I mean would always put the table that I want to include all records from on the *LEFT*, and use a left join This makes my query writing and reading consistent since I know if I'm using an outer join it will always be a left outer join.

 

[bearcub]

Ii'm just trying to understand how the inner and outer joins do their magic.

An inner join has two fields of the same name. If either field has something in it ((regardless whether or not the other table has something in it) the field will show up in the query or report. If they're both empty, though, nothing will be displayed, correct?

For an outer join (left), the row is only returned where there is something in the source table. Meaning that if the secondary table has something in it will only be displayed if there it matches what is in the first table. Is this how it works?

It's one of those situations where I think I know something when I'm learning it then months later I'm not sure if I understand t correctly.

Michael

 

[xenou]

An inner join has two fields of the same name. If either field has something in it ((regardless whether or not the other table has something in it) the field will show up in the query or report. If they're both empty, though, nothing will be displayed, correct?

No, the value in the field that is joined must match. It can't be in "either" table, it must be in "both" tables.



If it helps, just think of some primitive, obvious example:
first table Orders
second table OrderLines

The first table (Orders) will join to the second table (OrderLines) on OrderID = OrderID. Only the order lines that match the same orders will be returned in the results.

If you don't like orders, just pick something else: Books, and BookAuthors; BankAccount and BankAccountTransactions; Students and StudentClasses. You always want a match where the ID in both tables is the same.

 

[bearcub]

Okay, I think I see your point - where they both match.

In the left outer join, the left table takes precedence. But this is where I''m missing something. Starting with left table, it looks to see if there is a match in the right table. If it finds a match then return it.

Wouldn't this return the same result as an inner join because they are only going to return something if the right table matches the left table. For some reason, I can't explain it, the result would appear to be the same (I know it isn't. I haven't some sort of mental block here).

Can you give me an example for the second case.

I've had queries in databases where I could only use the Join 2 type not the join 1. I know it works but I'm having a fuddy duddy brain block.

Thank you for your help and patience,

Michael

 

[RoryA]

It returns all the records from the left table, plus any matches from the right. So if there are records in the left table without matches in the right, they would still appear, unlike with an inner join.

Think of insurance policies in the left table and claims in the right. Not all policies have claims. An inner join would only return policies with claims; a left join returns all policies, plus claims information for those policies that have had them.

 

[bearcub]

Thank you, that makes it clear now. I thought it was something like that but I it wasn't as clear as the insurance example. I think I got it now.