Creative joins, anything to keep in mind - "gotchas", etc?

[OuterApply]

I can do this:

Select * from

Table1
Left join
(
table2
inner join table3 on blah blah
) on table[2 or 3].column = table1.column


It can be a handy way to left join to 2 tables that, themselves, almost-always/always need to be inner joined to each other.

Does anyone know of any "gotchas" - things to keep in mind, caveats, warnings, etc?

 

[The_Doc_Man]

Multi-layered JOINs have implied directions in many cases. INNER JOIN is usually pretty good about this, but LEFT or RIGHT JOIN don't mix too well with each other. If you have a formal relationship with an asymmetric JOIN, you can call up the Relationship view (or if you are building this in a query, in the QBE grid) and see arrows at the end of the relation lines. If you have a JOIN of a JOIN, there are THREE tables (two JOINs). In that case, the table that is in the middle of the sequence must have consistent arrow directionality with the next JOIN in sequence. Like, you can't mix LEFT JOIN and RIGHT JOIN in the same complex query.

INNER JOIN doesn't suffer from directionality issues but is exclusionary if there is actually a case where a foreign key (FK) doesn't exist to match the prime key (PK) of a JOINed table, so if you tried to figure out ahead of time how many elements you predict for the table, that number might be a bit off due to the exclusionary nature of an INNER JOIN.

If you have a JOIN with ANY directionality in it AND there is a chance that some fields will be absent (thus represented by NULL), such queries might not be updateable. Another reason to not be updateable would be if you have a query with a many-to-one JOIN relationship and your selector is ONLY a field from the one-side of that query. Your query will return the correct ONE side but ANY matching row from the MANY side, and this makes for an ambiguous update.

 

[theDBguy]

Hi. Welcome to AWF!

As long as you can segregate the joins you're trying to create from each other to avoid ambiguity, then I think there are no gotchas to worry about other than the updatability of the data already mentioned.

 

[isladogs]

See if my article on query joins is helpful. It covers inner, outer, mixed, Union and ambiguous joins....

Query Join Types

This article explores the different types of select query joins available in Access and how these affect the query output in each case. In addition it discusses the causes of ambiguous join errors and how to solve these

www.isladogs.co.uk

@The_Doc_Man
I accept the principle of what you were saying but in fact you can have both left and right joins in the same query.
See the above article for more than one example

 

[CJ_London]

I would add that left and right joins are generally slower than inner joins. They certainly have their uses but only use them when required.

 

[OuterApply]

@CJ_London Left join slower than inner join? in sql server? I think the opposite

 

[Galaxiom]

I would add that left and right joins are generally slower than inner joins. They certainly have their uses but only use them when required.

I use LEFT JOIN in SQL Server in preference for joins to lookup tables in case the value is not in the lookup table. (I have no control over referential integrity with a linked server.)

I have never experienced any signs of degraded performance at all.

 

[Galaxiom]

Select * from

Table1
Left join
(
table2
inner join table3 on blah blah
) on table[2 or 3].column = table1.column

SQL Server doesn't need nested joins like Access.
Its only requirement is that the table being joined to is already listed before the join that uses it.

BTW
OUTER APPLY is a different from a LEFT or RIGHT OUTER JOIN.

SQL Server CROSS APPLY and OUTER APPLY Helpful Examples

Learn about SQL Server CROSS APPLY and OUTER APPLY and how they differ from regular JOINs and how to use these along with scripts and a video.

www.mssqltips.com

 

[The_Doc_Man]

See if my article on query joins is helpful. It covers inner, outer, mixed, Union and ambiguous joins....

Query Join Types

This article explores the different types of select query joins available in Access and how these affect the query output in each case. In addition it discusses the causes of ambiguous join errors and how to solve these

www.isladogs.co.uk

@The_Doc_Man
I accept the principle of what you were saying but in fact you can have both left and right joins in the same query.
See the above article for more than one example

Yes, you can, much of the time. But there are times where you cannot. I've run into them. Has to do with where the PKs are and how they are referenced. The "dependency" arrows cannot go both ways at once in the case of 1st JOIN being LEFT leading to 2nd table where the next JOIN is a RIGHT JOIN coming to the 2nd table from a 3rd table, because that leads to a multi-path case. You might be able to do SELECT but you would never be able to UPDATE because of the multi-path ambiguity. UPDATEs just HATE ambiguity of selection.

 

[CJ_London]

Left join slower than inner join? in sql server?

sorry - thought we were talking access sql - hadn't noticed this was posted on the sql server forum