Subqueries inside a select statement 4

[goofaholix]

I came across some code recently constructed with lookup subqueries nested inside the select statement. I haven't seen this approach before and it strikes me as an odd way of doing it, is it common? is it efficient? Why would you choose this approach over joining the tables in the FROM statement?

Example;

Select
ID,
(select Location from tblLocation where ID=a.LocID) as Location,
(select AgeBracket from tblAgeBracket where ID=a.AgeID) as AgeBracket,
(select Status from tblStatus where ID=a.StatusID) as Status,
From tblMain as a

Thanks

Bruce





Bruce
<a href="

http://homepages.woosh.co.nz/bf/">http://homepages.woosh.co.nz/bf/</a>

 

[jbenson001]

Is this just part of the SQL? I ask because it makes no sense. Nothing is joined and you will get random IDs from tblMain for each row. Also, each of the sub-select values will be the same for each row(the number of rows in tblMain)

 

[tsuji]

Check out this and links therefrom too.

http://msdn.microsoft.com/en-us/library/ms189575.aspx

It should be a proper select query as long as all the subqueries return each a single value. (In fact, there are quite a bit of rules governing the use of subqueries in different context, see mentioned ref.)

 

[SgtJarrow]

I would re-write this query to something like this:

SELECT
	a.ID
	, l.Location
	, ab.AgeBracket
	, s.Status
FROM tblMain a
INNER JOIN tblLocation l
	ON a.LocID = l.ID
INNER JOIN tblAgeBracket ab
	ON a.AgeID = ab.ID
INNER JOIN tblStatus s
	ON a.StatusID = s.ID

I find this to be the more common approach and I also find it much easier to read. It also makes a bit more sense, as jebensen was referring to. This explicitly defines the joins in a simple to read manner. As tsuji said, the other "should" work, but if the queries do not return a single value, there will be some odd results...

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
C#.NET Programmer

 

[gmmastros]

mstrmage1768

Your query is not the same as the original (obviously). But, under certain circumstances, it won't return the same data, either. Specifically, your query is not a valid replacement for the original. I encourage you to take a look at this and try to figure out why it's not the same. For example, under what data circumstances would the 2 queries return different results.

If you are not sure, let me know and I will explain (although I would prefer for you to figure this out on your own).

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SgtJarrow]

gmmastros,

Except for the INNER JOINs possibly causing a row to not be returned due to NULLs etc, I am not seeing the problem. The INNER JOINs could be modified to LEFT JOINS (and some COALESCEs as needed) to ensure valid returns. And/or ensuring the data in tblMain is fully validated against data in the joined tables at INSERT/UPDATE could also help, but I think that is not what you are asking about either.

As a developer always looking to expand my knowledge, I must ask you pass your expert knowledge on to me (and others) - because I cannot see the error of my ways...

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
C#.NET Programmer

 

[gmmastros]

First, let's take a looks at the following code. It probably represents the 'normal' situation.

Declare @Main Table(ID Int, StatusId Int)

Insert Into @Main Values(1,1)
Insert Into @Main Values(2,1)
Insert Into @Main Values(3,1)
Insert Into @Main Values(4,2)

Declare @Status Table(Id Int, Status VarChar(20))

Insert Into @Status Values(1,'Red')
Insert Into @Status Values(2,'Yellow')
Insert Into @Status Values(3,'Green')

Select ID,
       (Select Status From @Status Where Id = StatusId)
From	@Main

Select	Main.Id, Status.Status
From    @Main As Main
        Inner Join @Status As Status
           On Main.StatusId = Status.Id

With the data presented in the table, both queries return the same output.

Now, let's change some data. You already mentioned about the NULLs and using Left join instead. But let's see what happens.

If we leave the queries alone and just change the data:

Declare @Main Table(ID Int, StatusId Int)

Insert Into @Main Values(1,1)
Insert Into @Main Values(2,1)
Insert Into @Main Values(3,[!]NULL[/!])
Insert Into @Main Values(4,2)

Declare @Status Table(Id Int, Status VarChar(20))

Insert Into @Status Values(1,'Red')
Insert Into @Status Values(2,'Yellow')
Insert Into @Status Values(3,'Green')

Select ID,
       (Select Status From @Status Where Id = StatusId)
From	@Main

Select	Main.Id, Status.Status
From    @Main As Main
        Inner Join @Status As Status
           On Main.StatusId = Status.Id

The first query returns 4 rows but the second query only returns 3 rows. Changing the second query to a left join instead...

Declare @Main Table(ID Int, StatusId Int)

Insert Into @Main Values(1,1)
Insert Into @Main Values(2,1)
Insert Into @Main Values(3,[!]NULL[/!])
Insert Into @Main Values(4,2)

Declare @Status Table(Id Int, Status VarChar(20))

Insert Into @Status Values(1,'Red')
Insert Into @Status Values(2,'Yellow')
Insert Into @Status Values(3,'Green')

Select ID,
       (Select Status From @Status Where Id = StatusId)
From	@Main

Select	Main.Id, Status.Status
From    @Main As Main
        [!]Left[/!] Join @Status As Status
           On Main.StatusId = Status.Id

Now we are back to returning the same data.

Now let's change the data again...

Declare @Main Table(ID Int, StatusId Int)

Insert Into @Main Values(1,1)
Insert Into @Main Values(2,1)
Insert Into @Main Values(3,NULL)
Insert Into @Main Values(4,2)

Declare @Status Table(Id Int, Status VarChar(20))

Insert Into @Status Values(1,'Red')
[!]Insert Into @Status Values(2,'Yellow')
Insert Into @Status Values(2,'Purple')[/!]
Insert Into @Status Values(3,'Green')

Select ID,
       (Select Status From @Status Where Id = StatusId)
From	@Main

Select	Main.Id, Status.Status
From    @Main As Main
        Left Join @Status As Status
           On Main.StatusId = Status.Id

Notice that we have 2 rows in the status table with an id = 2. In this case, the subquery method errors with "Subquery returned more than 1 value." The second query returns 5 rows because there are multiple matches in the status table.

We can fix the subquery method by adding Top 1 to it, like this:

Declare @Main Table(ID Int, StatusId Int)

Insert Into @Main Values(1,1)
Insert Into @Main Values(2,1)
Insert Into @Main Values(3,NULL)
Insert Into @Main Values(4,2)

Declare @Status Table(Id Int, Status VarChar(20))

Insert Into @Status Values(1,'Red')
Insert Into @Status Values(2,'Yellow')
Insert Into @Status Values(2,'Purple')
Insert Into @Status Values(3,'Green')

Select ID,
       (Select [!]Top 1[/!] Status From @Status Where Id = StatusId)
From	@Main

Select	Main.Id, Status.Status
From    @Main As Main
        Left Join @Status As Status
           On Main.StatusId = Status.Id

The Top 1 part prevents the error, but there is still only 4 rows in the first query and 5 rows in the second. And this is exactly the point I was trying to make. Joins will return ALL matches, but subqueries can ONLY return 1 value. Sure... it can be null if there are no matches, but it cannot return more than 1 match.

Also realize that this is a simple example because the subquery only involves 2 tables (the main table and a lookup table). Often times you will see more complicated queries as subqueries. The added complication makes it even more difficult to notice data discrepancies.

Of course, under the correct circumstances (based on data) the results can be the same, and you may even be able to rely upon it. To rely upon the results, the column in the lookup table that you are using should be unique, either enforced through a primary key or a unique constraint.

Make sense?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SgtJarrow]

Make so much sense. Knowing I was on the right track make me feel good about what I have learned from reading your many valued responses over the past years. Having the complete picture will hopefully help my development efforts in the future so I can avoid any pitfalls in my data gathering efforts. I will have to file this thread away for future reference as well.

I want to say thank you for both explaining this to me and all the other great answers you provide in this forum. You deserve more stars if I could give them.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
C#.NET Programmer

 

[jbenson001]

Awesome explination again as usual George. Want to come work with me? LOL

 

[goofaholix]

Judging by the responses it looks like my initial reaction was correct.

That this is an unusual way of writing SQL.

That it's only valid where there is a single value for each id in the lookup tables.

If so it's the equivilent of a left join.

I'd expect it's likely to run slower, or at least no faster, than the usual way of coding joins so I don't really see the point.

Bruce

http://homepages.woosh.co.nz/bf/

 

[sds814]

George,

In this example

Declare @Main Table(ID Int, StatusId Int)

Insert Into @Main Values(4,2)

Declare @Status Table(Id Int, Status VarChar(20))

Insert Into @Status Values(2,'Yellow')
Insert Into @Status Values(2,'Purple')

Select    Main.Id, Status.Status
From    @Main As Main
       INNER JOIN @Status As Status
           On Main.StatusId = Status.Id

what was the thinking process that sql went thru? Did it say that I don't know which 2 records Main should join Status with so let me join on both?

Thanks!

 

[gmmastros]

I don't know which 2 records

That makes it seem questionable, but there is nothing questionable about SQL. When you join tables, anything that matches the join will be returned in the results. More precisely, all rows that satisfy the join will be included in the results.

Often times, joins exist between a data and a lookup table. The lookup table usually has just one row per ID, so it's easy to think that inner joins match on just one row. This is only true because of the design and the data. SQL does not require this.

Does this make sense? It's really important that you understand this because joins are the basis of almost every query. If this does not make perfect sense, let me know and I will show you an example.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sds814]

It does.

But I'm sure your example will help me learn something new.