SELECT statements in INNER JOINs?
-
I've inherited a somewhat old system with a large number of SQL queries. In a number of these queries, there are SELECTs within INNER JOINs. For example, here are two INNER JOINs that are part of the same query: INNER JOIN ( SELECT * FROM Schema_types ) Schema_types ON Schema_types.Schema_typeID = Schemas.Schema_typeID INNER JOIN ( SELECT * FROM Marketeers WHERE MarketeerID = 7 ) Marketeers ON Marketeers.MarketeerID = Shipments.MarketeerID This looks rather peculiar to me. Is there merit in doing it this way, or is this just wrong?
-
I've inherited a somewhat old system with a large number of SQL queries. In a number of these queries, there are SELECTs within INNER JOINs. For example, here are two INNER JOINs that are part of the same query: INNER JOIN ( SELECT * FROM Schema_types ) Schema_types ON Schema_types.Schema_typeID = Schemas.Schema_typeID INNER JOIN ( SELECT * FROM Marketeers WHERE MarketeerID = 7 ) Marketeers ON Marketeers.MarketeerID = Shipments.MarketeerID This looks rather peculiar to me. Is there merit in doing it this way, or is this just wrong?
This does look peculiar. It may have been the result of never cleaning up after intial development. I don't know any benefit of writing them this way. There may not be any benefit in cleaning them up - except for readablity.
INNER JOIN Schema_Types ON (Schema_types.Schema_typeID = Schemas.Schema_typeID) INNER JOIN Marketeers ON ((Marketeers.MarketeerID = Shipments.MarketeerID) AND (MarketeerID = 7)) -
I've inherited a somewhat old system with a large number of SQL queries. In a number of these queries, there are SELECTs within INNER JOINs. For example, here are two INNER JOINs that are part of the same query: INNER JOIN ( SELECT * FROM Schema_types ) Schema_types ON Schema_types.Schema_typeID = Schemas.Schema_typeID INNER JOIN ( SELECT * FROM Marketeers WHERE MarketeerID = 7 ) Marketeers ON Marketeers.MarketeerID = Shipments.MarketeerID This looks rather peculiar to me. Is there merit in doing it this way, or is this just wrong?
Using subqueries (the SELECT statements you speak of) in INNER JOINS can be very useful. However, here it looks stupid because it is just doing a SELECT * FROM... and I guess this would be sub-optimal because it has to create an internal set of data for the subquery that contains every field and they may not all be used once the data gets into the main query. It would be better to do:
INNER JOIN Schema_types ON Schema_types.Schema_typeID = Schemas.Schema_typeID
INNER JOIN Marketeers ON Marketeers.MarketeerID = Shipments.MarketeerIDIf my understanding of the query optimiser is correct then this should also speed up your query.
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!