Need a 2nd opinion on a query

Question

Need a 2nd opinion on a query

dndaughtery

SSChampion

Points: 11290
More actions
November 26, 2013 at 12:32 pm

#282850

I had a collegue write the following query. He got defensive when I asked what he was trying to accomplish with it.
Select count(*) from tblevent a inner join tblevent b on a.omsid = b.omsid And a.Recordtype = 'promo' and b.recordtype = 'event'
The column recordtype can only be 'promo' or 'event'.
Im thinking this is performing a join on one table joining with itself based on OMSID matching, then filtering out the records from table 'a' where recordtype was 'event' and filtering out table b where recordtype = 'promo'. either way I dont think he was getting anything of value.

Viewing 14 posts - 1 through 13 (of 13 total)

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

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

He's trying to count the events with Recordtype = 'promo' that have a corresponding event with Recordtype = 'event'. I don't know why, that's up to your needs.

The following might give you the same result, but I might be wrong since I don't know your data.

Select count(*)

from tblevent

HAVING COUNT(DISTINCT Recordtype) = 2

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

TomThomson SSC Guru Points: 104773 More actions · Answer 2

Luis Cazares (11/26/2013)
He's trying to count the events with Recordtype = 'promo' that have a corresponding event with Recordtype = 'event'. I don't know why, that's up to your needs.
The following might give you the same result, but I might be wrong since I don't know your data.
Select count(*)
from tblevent
HAVING COUNT(DISTINCT Recordtype) = 2

I think you are missing GROUP BY omsid or something like that there.

I agree it's fairly likely that that's what he's trying to do, but it might be a bit more complicated - as you say, it depends on what the data are.

Tom

jcb SSCertifiable Points: 6574 More actions · Answer 3

I guess there are not to worry about unless there are many records with the same omsId and recordType, this can cause a cartesian product in that self join as show in this example:

http://sqlfiddle.com/#!6/35f82/1

Of course we can tell more if we know more about that tables and data.

Aram Koukia SSCommitted Points: 1521 More actions · Answer 4

dndaughtery (11/26/2013)
I had a collegue write the following query. He got defensive when I asked what he was trying to accomplish with it.
Select count(*) from tblevent a inner join tblevent b on a.omsid = b.omsid And a.Recordtype = 'promo' and b.recordtype = 'event'
The column recordtype can only be 'promo' or 'event'.
Im thinking this is performing a join on one table joining with itself based on OMSID matching, then filtering out the records from table 'a' where recordtype was 'event' and filtering out table b where recordtype = 'promo'. either way I dont think he was getting anything of value.

He is trying to count the rows with RecordType = 'Promo' only if there is a row with the same omsId and RecordType is 'Event'

[font="Courier New"]Aram Koukia: http://www.koukia.ca[/font]

pierre-702284 SSC Veteran Points: 216 More actions · Answer 5

Assuming that omsid and Recordtype form a unique composite index, he is trying to find a count of omsid's that have both a 'promo' and an 'event' Recordtype.

ojrodriguez SSC Enthusiast Points: 177 More actions · Answer 6

Could it be that what he is trying to accomplish is the following?

Select count(*)

from tblevent a inner join tblevent b on

a.omsid = b.omsid

where Recordtype in( 'promo', 'event')

alexgorod SSC Enthusiast Points: 160 More actions · Answer 7

Actually, it's probably

Select count(*)

from tblevent a inner join tblevent b on

a.omsid = b.omsid

where a.Recordtype = 'promo'

and b.Recordtype = 'event'

Luis Cazares SSC Guru Points: 183698 More actions · Answer 8

And that's the original query.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

alexgorod SSC Enthusiast Points: 160 More actions · Answer 9

Well, not really. I think that it's much better to separate "join logic" from "business logic" where one belongs to ON and another to WHERE. It's a matter or coding style, and it's easier to understand what the code does.

ojrodriguez SSC Enthusiast Points: 177 More actions · Answer 10

That's correct but at the WHERE Clause there is just one result set to filter, the result of the join, no need to filter on both tables.

TomThomson SSC Guru Points: 104773 More actions · Answer 11

ojrodriguez (12/3/2013)
That's correct but at the WHERE Clause there is just one result set to filter, the result of the join, no need to filter on both tables.

Because a select query is declarative, the optimiser can in principle choose to apply filter conditions to that involve only one component of the join either before the join or after it, and not neccessarily both before or both after. The optimiser should be considering things like which plan leads to fewest rows being processed - and ideally if the cardinality of the join's result is estimated to be sufficiently greater than the sum of the cardinalities of the two tables it should do the filtering before the join whichever way the query is written. Equally if the cardinality of the joined table is sufficiently smaller than the sum of the two table's cardinalities it should in principle do the filtering after the join, even if the query is written to suggest that it shouldn't, like this:Select count(*)

from (Select omsid, recordType from tblevent where Recordtype = 'promo') a

inner join

(Select omsid, recordType from tblevent b where Recordtype = 'event') b

on a.omsid = b.omsid

That's still the same query because it still specifies the same resulting rowset (a single row with the required count as its only column).

The system is not trying to undertake some execution pattern implied by the way the query is written; it is trying to obtain the recordset specified by the way the query is written. People who fail to understand that often end up devising some fiendishly complex way of writing what should be a simple query, creating problems for future maintenance and enhancement and possibly even making it so complex that it fools the optimiser into doing the wrong thing (delivering poor performance), whereas writing the query in a clear and straight forward manner obviates all those problems. Note that here the optimiser is also entitled to ignore the projections that, if a select statement had a procedural meaning rather than a declarative one, would be implied if the filters were expressed this way, since those projections have no impact on the result.

Tom

ojrodriguez SSC Enthusiast Points: 177 More actions · Answer 12

ojrodriguez

SSC Enthusiast

Points: 177

December 4, 2013 at 12:50 pm

#1670832

Well said! I just try to keep it. Simple.

John Chapman SSC-Addicted Points: 457 More actions · Answer 13

Moving some of the join logic to the WHERE clause is only equivalent when the join type is INNER. As soon as OUTER joins are introduced, that equivalence is gone.