Variable based JOIN

Question

Post reply

Variable based JOIN

rjv_rnjn

Hall of Fame

Points: 3023
More actions
March 29, 2012 at 1:55 pm

#266382

I am working on a SQL query that takes a variable to base the join on. The same variable is used for ORDER BY clause. They had ensured earlier that there were would be no sort order (ASC or DESC) defined in the parameter passed. So I was modifying the query to work for paging and sorting on a UI grid and was surprised to find that the query does not break if I pass the value for variable as "col1 DESC".

Here's how the query looks

select a.* from tableA a
left join tableB b on b.ColA = @variable

If I do set @variable = 'col1 desc' it works. I expected it to throw some error.

Can anyone explain what's going on here?

Now that I've written the question, to me it seems like this isn't a join but more of a WHERE clause. Am I right?

Thanks.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286562 More actions · Answer 1

rjv_rnjn (3/29/2012)

I am working on a SQL query that takes a variable to base the join on. The same variable is used for ORDER BY clause. They had ensured earlier that there were would be no sort order (ASC or DESC) defined in the parameter passed. So I was modifying the query to work for paging and sorting on a UI grid and was surprised to find that the query does not break if I pass the value for variable as "col1 DESC".

Here's how the query looks

select a.* from tableA a
left join tableB b on b.ColA = @variable

If I do set @variable = 'col1 desc' it works. I expected it to throw some error.

Can anyone explain what's going on here?

Now that I've written the question, to me it seems like this isn't a join but more of a WHERE clause. Am I right?

Thanks.

Not really sure why you are using a left join to tableB but nothing is in the select list. Syntactically this will work with no problem but I don't think it is doing what you think it it. It will return b rows where b.ColA = 'col1 desc'

Your query does not have an order by so if they were returned in the correct order it was by chance.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286562 More actions · Answer 2

Of course your join also has nothing to actually join tableA and tableB. :w00t:

Here is an example of what is happening.



declare @variable varchar(20) = 'col1 desc'

create table #tableA

(

	SomeValue varchar(10)

)

create table #tableB

(

	ColA varchar(10)

)

insert #tableA select 'MyValue'

insert #tableB select 'Your Value' union all select 'col1 desc'

select a.*

from #tableA a

left join #tableB b on b.ColA = @variable --really should have something to correlate the tables in your join

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

rjv_rnjn Hall of Fame Points: 3023 More actions · Answer 3

Sean Lange (3/29/2012)

Of course your join also has nothing to actually join tableA and tableB. :w00t:

Thanks for confirming.

I simplified the query that is in there but this had me pondering for a while and thought should ask somebody.

And no I did not commit the crime and I can not re-write that. It's a strict no-no where I work. I will have to make do with it. :angry: