Creative joins, anything to keep in mind - "gotchas", etc? (1 Viewer)

OuterApply

New member
Local time
Yesterday, 18:03
Joined
Mar 10, 2022
Messages
15
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

Immoderate Moderator
Staff member
Local time
Yesterday, 20:03
Joined
Feb 28, 2001
Messages
27,001
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

I’m here to help
Staff member
Local time
Yesterday, 18:03
Joined
Oct 29, 2018
Messages
21,358
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

MVP / VIP
Local time
Today, 01:03
Joined
Jan 14, 2017
Messages
18,186
See if my article on query joins is helpful. It covers inner, outer, mixed, Union and ambiguous joins....

@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
 
Last edited:

CJ_London

Super Moderator
Staff member
Local time
Today, 01:03
Joined
Feb 19, 2013
Messages
16,553
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.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 12:03
Joined
Jan 20, 2009
Messages
12,849
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

Super Moderator
Staff member
Local time
Today, 12:03
Joined
Jan 20, 2009
Messages
12,849
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.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 20:03
Joined
Feb 28, 2001
Messages
27,001
See if my article on query joins is helpful. It covers inner, outer, mixed, Union and ambiguous joins....

@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

Super Moderator
Staff member
Local time
Today, 01:03
Joined
Feb 19, 2013
Messages
16,553
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
 

Users who are viewing this thread

Top Bottom