Link to home
Start Free TrialLog in
Avatar of marrowyung
marrowyung

asked on

complex correlated subqueries, inline functions with data access, functions in the where clause

hi,

I read from some web site that complex correlated subqueries, inline functions with data access, functions in the where clause is not good in T-SQL programming, is it real ? any URL reading for real world case ?
SOLUTION
Avatar of Vikas Garg
Vikas Garg
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of marrowyung
marrowyung

ASKER

"Using sub-queries cab be replaced with Joins
Since subquery is row-by-row operation while join is set based operation,"

good !

how about function in where clause ?
"The same way using function is also time consuming which should be replaced with query if possible
Since for function calculation each row has to go to call the function."

replaced with what kind of query ? please give example.
Hi,

For simple example if you have a function which combines first name and last name

select *, dbo.funconcate(firstname,lastname) from table

can be replaced with select *, firstname + ' ' + lastname from table
marrowyung

Do us a favor and ask a single actionable question.  Asking multiple vague questions is really not going to encourage experts to respond, as it's not certain when we've answered the question, and experts will be wondering exactly how many follow-up questions there will be before points are awarded.

​Top 10 Ways to Ask Better Questions, Number Nine..  Ask a question, don't read a fairy tale!, among others.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Vikas Garg,

"select *, dbo.funconcate(firstname,lastname) from table

can be replaced with select *, firstname + ' ' + lastname from table"

so this example what it means is the function only place the firstanme and lastname together, this example is too simple and I don't think anyone will need to build a function like this, right? some bit more complex example and how to solve it?

"http://javarevisited.blogspot.in/2012/07/subquery-example-in-sql-correlated-vs.html"

this one seems only for MySQL but not MSSQL, right?

Paul Maxwell,

"a "correlated subquery" forms a row by row operation & only some subqueries are correlated subqueries"

you mean the subquery can contain some query which is not needed anymore ?

"My point is that there are many good reasons to use subqueries in a from clause.
e.g. there are circumstance where using a  subquery to produce a "derived table" is the best approach"

Tks,  an example please. how subquies can be use as derived table to opimize the code for example.

how about  inline functions with data access ? wyy it is no good ?

"using functions on data in the where clause removes the ability to use indexes and forces table scans instead. "

tks, this is a very good statement, so we expected to see table scan/index scan if the where clause like that?

"SARGABLE  Search ARGument ABLE"

so you mean Non-sargable argument always include function in the where clause ?

but this example from your link:

"Non-sargable: SELECT ... WHERE Email LIKE '%wikipedia.org'"

the email column still indexable, right?
"a "correlated subquery" forms a row by row operation & only some subqueries are correlated subqueries"

you mean the subquery can contain some query which is not needed anymore ?

NO

I did provide an example as carefully as I could think of. A very common form of correlated subquery are those found in a select clause.  These subqueries have a where clause that relies on data somewhere else in the overall (outer) query. It is this FACT (a subquery being reliant on a value of the outer query) that DEFINES a "correlated subquery".

I repeat my earlier example, perhaps the extra words above will make it apparent why I used inner (inside the subquery) and outer (outside the subquery)

e.g. select ..., (select top (1) x from inner where inner.y = outer.z order by x) from outer
notice how that where clause crosses from inside the subquery to some table in the outer query, THAT is the indication of a "correlation", and that is why it forces a row by row operation.

Please review my comments on the question regarding derived tables and CTEs, there are examples of subqueries there.

so you mean Non-sargable argument always include function in the where clause ?

In most cases, yes, this is true.
I cannot say it is always true because someone will find some reason why it isn't

I did not write the examples at Wikipedia. I believe the authors would be attempting to illustrate a general principle that a LEADING wildcard can invalidate the use of an index, but a TRAILING wildcard does not. However different databases have different ways of dealing with this, I just do not want to start a long conversation on an example I didn't write.
Paul Maxwell,

you are explaining good tks.