In the following query…
select * from table2 where field1 not in (select distinct field1 from table1 where date >= 2007-01-01)
does the subquery get run once? Or does it get run again each time field1 needs to be checked?
The subquery will be run once. The results are stored in a temporary table (or in memory if the result set is small) and field1 is checked against that.
The general answer to your question is (meaning how are subqueries processed): it depends.
Which database are you using, what are the specifics of the query, etc. Oracle, SQL Server and DB2 don’t optimize/process this stuff the same way. For example, there are times when SQL Server will process the nested query once for every row of the outer query.
From this site…
A correlated subquery is a SELECT statement nested inside another T-SQL statement, which contains a reference to one or more columns in the outer query. Therefore, the correlated subquery can be said to be dependent on the outer query. This is the main difference between a correlated subquery and just a plain subquery. A plain subquery is not dependent on the outer query, can be run independently of the outer query, and will return a result set. A correlated subquery, since it is dependent on the outer query will return a syntax errors if it is run by itself.
A correlated subquery will be executed many times while processing the T-SQL statement that contains the correlated subquery. The correlated subquery will be run once for each candidate row selected by the outer query. The outer query columns, referenced in the correlated subquery, are replaced with values from the candidate row prior to each execution. Depending on the results of the execution of the correlated subquery, it will determine if the row of the outer query is returned in the final result set.
OTOH, a regular subquery (i.e. non_correlated) can be run as a stand-alone because it has no dependent values in the outer query. The optimizer will run it once and save the value set to run against the outer query. Any RDBMS that doesn’t do this probably went out of business in the 1980’s.
In fact, for the correlated type, the optimizer might be smart enough to run the inner query once and save a table of results that will be “joined” against the outer query.
Just FYI…if you want a little bit of insight into your MS SQL queries, and you have Query Analyzer (MSSQL2000) or Management Studio (MSSQL2005), type your query into the window and right-click on the query window to choose the “Display Execution Plan” option. Sort of shows you how things are going to be laid out.
[hijack]
I just wanted to pop in to thank K364 for his (?) help in my Access thread a while back. Due to a bazillion reasons and other projects I haven’t had the time to get things working as I wanted to, but I should be able to get back to it this week, and I’m glad I have your advice! Thanks again!
[/hijack]
I am a senior developer/analyst and my current company uses subqueries in every way imaginable all over the place. We have jobs that fire off 20+ subqueries for every record and that means that literally millions of Oracle queries are fired off for one job. Still, if they are constructed the right way, all of them combined will run in less than 30 seconds and usually much quicker than that. We have had many new analysts that see this and want to optimize the process. It isn’t worth it. Most of our Oracle jobs are not transactional and it doesn’t matter if they run in 2 seconds or 30 seconds. A huge number of queries can run in a short time so you have to pick your battles.
Mnemosyne You’re welcome, good luck when you return to the battle. Give us a shout if things get sticky!
Shagnasty Great point - unless they’re transactional (or take forever to run) don’t worry about optimizing - if the indexes are there, and the statistics are up to date, Oracle will almost always do the right thing - let’s not make the assumption that subquerys are going to derail the optimizer. Anyway, I prefer to write SQL that’s as clear as possible, and subquerys usually do that.
What’s always fun is when you “optimize” your query, and then it turns out the query analyzer in your DBMS already figured out the same thing and they end up exactly the same.
Thanks for the replies everyone. It’s useful to know that most of the subqueries I do will be run once.
I read the thread at home, and I was itching to try this out. I’m at work now. display execution plan does nothing, but ‘show estimated execution plan’ does show it.
I put in one of my queries just to see what it would look like. A very complex query by my level of skill, and 99% of the cost is from the most basic bit of it! (probably because the most basic bit is pulling data from a truly MASSIVE table)
I actually have some heavy thinking to do in the next day…as of this afternoon, I have the filename popping up in a Before Update messageboxn from a subform, which is fantastic. I just got hung up on one little detail, and I’ll be tackling it tomorrow. I need to better understand how forms and subforms communicate!
Basically, I have the equation with something like Filename=Me!Parent.Mainform!Country & Me!Parent.Mainform!DocumentNumber… and so on, but for one control, I need to use an iff statement to remove the / that people are likely to type if they enter revision number N/A. I have the IFF working correctly if I do the update in the Main form, or in a textbox on a report later, but using the same Me…Parent structure inside the IFF brackets doesn’t seem to work. Offhand, I don’t recall if I’ve used Me!Whatever.Value or not. I think I’ve tried both.
But since I don’t have it in front of me, I can’t even post it for advice, so I’ll just have to think… think… think! I hope it’s just a typo somewhere!
I can’t believe how much I’ve actually understood of this thread, though! A month ago I would have passed right over it! It’s weird… I was once a biochemist… now I’m an Engineering student learning to program/develop a database!
Mnemosyne - Good point - you should actually check for all of the following: \ / : * ? " < > |
You have two choices: disallow the typing of those characters into the text boxes, or make substitutions when the file name is built.
This works in the Before Update of my main form and in a textbox on a report I generate:
Filename = Me.CountryID.Value & Left(Me.TypeDoc.Value, 3) & Me.NoDoc.Value & "R" & (IIf([Revision] = "N/A", "NA", [Revision])) & "N" & Me.CertificationIndex.Value
But this does not work in the Before Update of my subform:
Filename = Me.Parent!CountryID.Value & Left(Me.Parent!TypeDoc.Value, 3) & Me.Parent!NoDoc.Value & "R" & (IIf([Me.Parent!Revision] = "N/A", "NA", [Me.Parent!Revision])) & "N" & Me.Parent!CertificationIndex.Value
If I remove the IFF statement and just put in Me.Parent!Revision.Value, that works, but I get the / if it is entered on data entry. Any permutation (with or without .Value, removing Parent, whatever) I can think of in the subform gives me the error that Access cannot find the control I’m looking for.
I also need to switch CountryID to its associated Country name and then I’ll be done with this stupid filename!
Just eyeballing your code it should work… you may have discovered a bug with the use of the IIF function. Or perhaps a naming conflict (are there two “things” named Revision? - I would consistently name textboxes as txtCountryID, txtRevision etc)
Actually, IIF in the VBA environment is, well to me, a bit odd since the If/Then/Else statements are so clear and standard. (However, you do have to use IIF in Queries/Forms/Report properties/parameters where only an expression can be used, i.e. not multi-line code)
I would do something like:
Dim sCountryID as String, sTypeDoc as String, (etc)
sCountryID = Me.Parent!CountryID.Value & ""
sTypeDoc = Me.Parent!TypeDoc.Value & ""
sNoDoc = Me.Parent!NoDoc.Value & ""
sRevision = Me.Parent!Revision.Value & ""
If UCase(sRevision) = "N/A" Then sRevision = "NA"
sCertificationIndex = Me.Parent!CertificationIndex.Value & ""
Filename = sCountryID & _
Left(sTypeDoc, 3) & _
sNoDoc & _
"R" & _
sRevision & _
"N" & _
sCertificationIndex
The & “” stuff may look a bit odd, but if the textbox has no entry then the assignment of the .Value to the string will bomb since a string cannot be NULL, however it can hold an empty string, which is the “” part. (Yes, there is a difference between an empty string and a NULL) Another way to do it is to use Variants instead of Strings - Variants CAN be NULL and you can test that with the IsNull function. But I prefer strings for various reasons
Well, I’m starting to blather so I’ll sign off for now, hope this works, let me know!
Showers K364 with flowers, champagne and bacon-flavoured delicacies
That worked perfectly; thank you! I even tweaked it a bit to only add the R is the revision is a number, otherwise to put NA. (*) I am so happy to have this working! Seriously, a month ago I would not have thought myself able to do half of this stuff, so I am delieriously happy about it!
I only had the one box named Revision, but you’re right that I should have named them txtX, cboY, etc, so I’ve done that. It’s a bad habit when I begin with a form from the wizard to not go in and change things.
*Using this code:
sRevision = Me.Parent!txtRevision.Value & “”
If sRevision = “N/A” Then sRevision = “NA” Else sRevision = “R” & sRevision
happily skips out of the thread