SQL nested queries question

[stoolpigeon]

I understand the following query setup:

SELECT *
FROM tbl1
WHERE field1 = (SELECT field1 FROM tbl2 WHERE field2 = x)

Essentially the nested select statement is executed and returns a value for field1 which is then used in the 'main' select statement.

What I don't understand is the following:

SELECT *
FROM Employee Emp1
WHERE (N-1) = (
SELECT COUNT(DISTINCT(Emp2.Salary))
FROM Employee Emp2
WHERE Emp2.Salary > Emp1.Salary)

I'm struggling with the idea that there's some kind of looping going on? The inner select statement is being executed for each Employee in Emp1 in the outer statement. This seems completely different from the first method of nestling a select statement above.

It seems to me to be similar in structure to when I use, in VBA:

for i = x to y
for j = w to z
*statement*
next j
next i

where i is fixed until j has finished iterating and then we move to the next i etc but I don't really follow how SQL is doing this?

 

[CJ_London]

Your first query should be more along the lines of

SELECT * 
FROM tbl1 
WHERE field1 [COLOR=red]IN[/COLOR] (SELECT field1 FROM tbl2 [COLOR=red]AS Tmp[/COLOR] WHERE field2 = [COLOR=red]tbl1.field2[/COLOR])

Your sub query is constructed so it could return more than one record. This is OK in a criteria but if you were using a subquery to return a value (the equivant of DLookup, DMax etc), it must be constructed so it can only return one value otherwise an error is reported when you try to run the query - hence the use of IN in this case

You need to use AS Tmp (or another name to suit yoruself) to differentiate the tables used in the subquery from those used in the parent query

And normally you use some reference to the parent query - hence =tbl1.field2

Subqueries do not do iterations as you are suggesting. As stated before, they are similar to Dlookup, DMax, Dfirst etc functions but can be much more complex and typically work faster than these functions.

I've tried to interprete what you are trying to do and I think you simply want a count for each employee of how many employees earn more than they do.

If this is the case then your query should be

SELECT *, 
(SELECT COUNT(Salary) FROM Employee AS TMP WHERE Salary > Emp1.Salary) AS EarnMore 
FROM Employee

If you have a specific requirement in mind, perhaps you can explain what it is you are trying to do. Unfortunately the code you have provided does not make sense

 

[stoolpigeon]

I don't have a specific requirement in mind, I'm just trying to learn some higher end SQL and saw this online:

..index.php/database-sql/find-nth-highest-salary-sql

at programmerinterview.com

and am consequently trying to get my head around what it's doing

 

[stoolpigeon]

I took the second piece of code from a website (apparently can't paste websites here otherwise I'd give you a direct link!). It's essentially trying to calculate the nth highest salary and the website explains it as working in the following way:

"The query above can be quite confusing if you have not seen anything like it before – the inner query is what’s called a correlated sub-query because the inner query (the subquery) uses a value from the outer query (in this case the Emp1 table) in it’s WHERE clause.
The most important thing to understand in the query above is that the sub-query is evaluated each and every time a row is processed by the outer query. In other words, the inner query can not be processed independently of the outer query since the inner query uses the Emp1 value as well. "

"From a very high level, the way the query above works is by finding the number of salaries that are greater than the current salary being searched for. If that number is exactly equal to N-1, then we know that we have the Nth highest salary, otherwise we continue searching through the table. If this description does not make sense, then read on for a more detailed description, which should make sense to you. "

I've highlighted in bold the main part I'm struggling with.