January 17, 2013 at 4:56 am
this is simple query in sql server b'coz here retrive records count which is similarly records with the another table .:-D
January 17, 2013 at 5:18 am
Hugo Kornelis (1/17/2013)
Thanks for such a detailed explaination Hugo.
I think the below code
FROM Table1 AS a CROSS APPLY (SELECT Col1, Col2 FROM Table2 AS b WHERE b.Col3 = a.Col3) AS b
can be changed in this way.
FROM Table1 AS a CROSS JOIN (SELECT Col1, Col2 FROM Table2) b WHERE b.Col3 = a.Col3 AS b
OR
FROM Table1 AS a JOIN (SELECT Col1, Col2 FROM Table2) b ON b.Col3 = a.Col3 AS b
I hope both will give the same result. So we can achieve the expected result without cross apply.
But this is not possible in the case of User defined functions.
--
Dineshbabu
Desire to learn new things..
January 17, 2013 at 5:22 am
I guess what I'm not getting my head around is, what exactly is that subquery returning?
(select col1 from TblA2 where TblA1.col2 = TblA2.col2)
Is it returning TblA1.col1? If so that's reeeeeeally weird.
ron
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
January 17, 2013 at 5:29 am
Dineshbabu (1/17/2013)
Thanks for such a detailed explaination Hugo.I think the below code
FROM Table1 AS a CROSS APPLY (SELECT Col1, Col2 FROM Table2 AS b WHERE b.Col3 = a.Col3) AS bcan be changed in this way.
FROM Table1 AS a CROSS JOIN (SELECT Col1, Col2 FROM Table2) b WHERE b.Col3 = a.Col3 AS bOR
FROM Table1 AS a JOIN (SELECT Col1, Col2 FROM Table2) b ON b.Col3 = a.Col3 AS bI hope both will give the same result. So we can achieve the expected result without cross apply.
But this is not possible in the case of User defined functions.
If you add Col3 to the SELECT list of the subquery, then these are indeed equivalent. I used a simple example to demonstrate the principle. I have seen actual situations where this is not possible. For instance, you can not do without CROSS APPLY in the following fragment:
FROM Table1 AS a
CROSS APPLY (SELECT b.Col1, SUM(b.Col2) FROM Table2 AS b WHERE b.Col3 >= a.Col3 GROUP BY b.Col1)[/quote]
January 17, 2013 at 5:29 am
I guess what I'm not getting my head around is, what exactly is that subquery returning?
(select col1 from TblA2 where TblA1.col2 = TblA2.col2)
Is it returning TblA1.col1? If so that's reeeeeeally weird.
ron
Hi,
Yes, that is what it is returning.
January 17, 2013 at 5:43 am
Mark Grout (1/17/2013)
I guess what I'm not getting my head around is, what exactly is that subquery returning?
(select col1 from TblA2 where TblA1.col2 = TblA2.col2)
Is it returning TblA1.col1?
Yes, that is what it is returning.
Okay, so... since TblA1 isn't indicated as a source table in that subquery, I'm wondering how far you can take this. For example, if there was a TblA3 with a col4 field in the first position, same data...
select COUNT(1)
from TblA1
INNER JOIN TblA3
ON TblA1.col2 = TblA3.col2
where col1 in (select col4 from TblA2 where TblA1.col2 = TblA2.col2)
I know I could just try it, but I'd rather come at it from an academic standpoint. "No, that won't work, because..." would get me closer to getting my head around the concept. Does col4 resolve to TblA3.col3? If not, why not? If so... well, if so I guess I'm curious to know how much farther we can take it than that.
ron
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
January 17, 2013 at 6:11 am
Hugo Kornelis (1/17/2013)
For instance, you can not do without CROSS APPLY in the following fragment:
FROM Table1 AS aCROSS APPLY (SELECT b.Col1, SUM(b.Col2) FROM Table2 AS b WHERE b.Col3 >= a.Col3 GROUP BY b.Col1)
It can be done with Cross Join
FROM Table1 AS a
CROSS JOIN(SELECT b.Col1, SUM(b.Col2)AS Col2 FROM Table2 b GROUP BY b.Col1)B WHERE b.Col3 >= a.Col3
I don't know whether the cross join will fail based on data.
Anyway Hugo, I learnt the basic principle of cross join and cross apply from your explainations.
I think If I come across real time scenario only i will understand the full picture.
Thanks
--
Dineshbabu
Desire to learn new things..
January 17, 2013 at 6:11 am
What a great question. Thanks, definitely a learning moment for me.
January 17, 2013 at 6:16 am
ronmoses (1/17/2013)
Okay, so... since TblA1 isn't indicated as a source table in that subquery, I'm wondering how far you can take this. For example, if there was a TblA3 with a col4 field in the first position, same data...
select COUNT(1)
from TblA1
INNER JOIN TblA3
ON TblA1.col2 = TblA3.col2
where col1 in (select col4 from TblA2 where TblA1.col2 = TblA2.col2)
I know I could just try it, but I'd rather come at it from an academic standpoint. "No, that won't work, because..." would get me closer to getting my head around the concept. Does col4 resolve to TblA3.col3? If not, why not? If so... well, if so I guess I'm curious to know how much farther we can take it than that.
SQL Server will first try to "bind" (that's the official term) col4 in the subquery. Only TblA2 is visible there. If there is a col4 in that table, it is used. Otherwise, SQL Server will continue the process by moving one scope up - in this case the complete query. TblA1 and TblA3 are visible there. If there is exactly one col4 in them, it will be used. If there are two columns with that name, you get the "ambiguous column name" error. And if the column doesn't exist in either, you get the "column not found" error.
(*) Note that with nested subqueries, this process can be repeated multiple time, until the outermost layer has been reached.
(**) Also note that, though this is an interesting discussion from an academic point, you should simply not rely on this in your actual code. See preceding messages.
January 17, 2013 at 6:18 am
Dineshbabu (1/17/2013)
Hugo Kornelis (1/17/2013)
For instance, you can not do without CROSS APPLY in the following fragment:
FROM Table1 AS aCROSS APPLY (SELECT b.Col1, SUM(b.Col2) FROM Table2 AS b WHERE b.Col3 >= a.Col3 GROUP BY b.Col1)
It can be done with Cross Join
FROM Table1 AS aCROSS JOIN(SELECT b.Col1, SUM(b.Col2)AS Col2 FROM Table2 b GROUP BY b.Col1)B WHERE b.Col3 >= a.Col3
I don't know whether the cross join will fail based on data.
It will fails based on syntax. The outer WHERE clause references b.Col3, but the subquery aliased as b only exposes the columns b.Col1 and b.Col2, so you'll get a "column not found" error.
And you cannot fix this by adding Col3 to the SELECT list of the subquery, because of the GROUP BY - only columns in the GROUP BY list or in an aggregate function are allowed in the SELECT list.
January 17, 2013 at 6:45 am
Hugo Kornelis (1/17/2013)
It will fails based on syntax. The outer WHERE clause references b.Col3, but the subquery aliased as b only exposes the columns b.Col1 and b.Col2, so you'll get a "column not found" error.
And you cannot fix this by adding Col3 to the SELECT list of the subquery, because of the GROUP BY - only columns in the GROUP BY list or in an aggregate function are allowed in the SELECT list.
I'm really sorry.. I didn't notice that part..
--
Dineshbabu
Desire to learn new things..
January 17, 2013 at 7:04 am
Hugo Kornelis (1/17/2013)
Yes, this is standard. And yet, I still would not rely on it. Imagine how your query would suddenly fail if someone adds a column "col1" to table "TblA2"!For any query that uses more than a single table, you should always use the table prefix on ALL column references. (And because most table names tend to be long and I prefer my query to be human readable, this automatically implies that you should also always provide an alias for each of the tables used in the query.
I got this question wrong because I assumed it was a trick, and it was... but I followed down the wrong trick path. 🙂
I second Hugo's sentiment above. Use a table alias every time you refer to a field. When there is more than one table the alias makes explicit which table the field is from. When there is only one table it makes clear tablealias.[field] references from [fieldalias]=expression references.
Perhaps most importantly it makes life a bit simpler for the maintenance programmer who is 'fixing' your brilliant code a year later; when table sizes exceeds some magic threshold and the query optimizer starts making bad decisions. In my experience the less I care about that maintenance phase, the more likely I'll be the one suffering through the cleanup.
January 17, 2013 at 7:19 am
Great question!
I used to use a similar query as an example for developers to show why they should always alias tables, especially in subqueries.
January 17, 2013 at 8:15 am
I got this when I ran it against a server running Microsoft SQL Server 2005 - 9.00.4053.00:
Results:
(No column name)
0
Messages:
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ','.
(1 row(s) affected)
May not be relevant to the logic of the result or correct answer, but does anyone know why this error happened when I tried it?
Thanks for any help,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
January 17, 2013 at 8:22 am
webrunner (1/17/2013)
I got this when I ran it against a server running Microsoft SQL Server 2005 - 9.00.4053.00:Results:
(No column name)
0
Messages:
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ','.
(1 row(s) affected)
May not be relevant to the logic of the result or correct answer, but does anyone know why this error happened when I tried it?
Thanks for any help,
webrunner
SQL 2005 didn't support inserting multiple rows using VALUES.
Viewing 15 posts - 16 through 30 (of 48 total)
You must be logged in to reply to this topic. Login to reply