Is it true that all types of joins reduce to Inner joins in the presence of the check of one table's pk = other table's fk?

Question

Is it true that no matter which type of join you use, if your WHERE clause checks one table's pk = other table's fk, it becomes same as an inner join as far as the result set is conncerned? In other words, if your sql query has some stuff like:

"Select ... from A Left join B On (...) E, F Full Outer Join G on (A.pk = G.fk) ... WHERE A.pk = G.fk and A.pk = B.fk and etc..."

In the above query, A is left-joined to B, whereas G is outer-joined to A on its fk. But as the where clause has the two checks, so the whole query reduces to something like:

"Select ... from A INNER JOIN B On (...) E, F INNER Join G on (A.pk = G.fk) ... WHERE etc ..." ?

The reason for asking this query is that I have many cartesian-type joins along with where clauses that are on one table's pk = other table's fk, that are slowing the query down. I was thinking of replacing the cartesian products with either Left Joins combined with keeping all the where clauses, or all Inner Joins.

Answer 1

Yes, that is a roundabout way of getting an inner join.

Think about how this works: It pulls everything from A, then the matching B's or nulls, then all G (nulls on the A/B and G sides), and then it essentially filters out everything that has a null side to it (the WHERE clause). So, yes, the INNER JOIN is probably faster.

A common source of this confusion is that this:

SELECT * FROM a INNER JOIN b ON a.id = b.id AND b.something = 'awesome'

is equivalent to:

SELECT * FROM a INNER JOIN b ON a.id = b.id WHERE b.something = 'awesome'

However, this same transitive approach doesn't work with full/outer joins, as it will do the join and then filter (depending on the filters, of course, and the optimizer).

So, in short: use inner joins if you really want an inner join.

Edit: I should also note that it's not the PK/FK deal, it's if you essentially repeat your ON clause in the WHERE clause that the full/outer joins will become equivalent to inner joins.

Answer 2

Yes, and you could also simply add

"Where PK Is Not Null" or 
"Where FK Is Not Null"

and this would also filter out the "extra" records from the "outer" side of the join...

Answer 3

Yes if you refernce the right side of a left outer join in a where clause with anything except "where myfield is null", you have created an inner join. This is because it filters out anything that that doesn't meet that condition including all the records that don't have a match to the intial table. Same with the other non-inner joins. To get around it you put the condition in the join. Example (this turns it into an inner join):

Select field1, field2 from mytable mt
left join mytable2 mt2 on mt.id = m2.id
where mt1.field1 = 'hello' and mt2.field2 = 'test'

Rewritten to preserve the left join:

Select field1, field2 from mytable mt
left join mytable2 mt2 on mt.id = m2.id and mt2.field2 = 'test'
where mt1.field1 = 'hello'

I also want to comment on something you said in a comment but figured my response would be too long for another comment.

"Select ... From A Inner Join B on (A.id = B.a_id),C,D, F where A.id=F.id" to: "Select ... From A Inner Join B on (A.id = B.a_id),C,D INNER JOIN F on A.id = F.a_id"

You do not want to combine syntaxes like this; this becomes very hard to maintain. In fact I recommend never using the old style comma syntax as it is subject to accidental cross joins. So in your example I would write to get the result set you currently get (at least someone maintaining will know you indended a cross join instead of did it by accident):

Select ... From A 
INNER JOIN B on A.id = B.a_id 
INNER JOIN F on A.id = F.a_id
CROSS JOIN C
CROSS JOIN D

Altenatively if the cross join is accidental, the code would change to:

Select ... From A 
INNER JOIN B on A.id = B.a_id
INNER JOIN F on A.id = F.a_id
INNER JOIN C on (fill in the join fields)
INNER JOIN D on (fill in the join fields)