Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
TomHand
Starting Member
8 Posts |
Posted - 2012-11-06 : 18:16:26
|
this is conventional JOINSELECT D1.deptno, D1.dnameFROM dept D1MINUSSELECT D2.deptno, D2.dnameFROM dept D2, emp E2WHERE D2.deptno = E2.deptnoORDER BY 1;this query can be rewritten in Anti JOINSELECT D.deptno, D.dnameFROM dept DWHERE NOT EXISTS(SELECT 1FROM emp EWHERE E.deptno = D.deptno)ORDER BY D.deptno;which query (Anti JOIN or JOIN) has a faster performance regradless of SQL engine platform (Orcle, SQL server, Mysql)? |
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-11-06 : 19:36:22
|
It's meaningless to state "regardless of SQL engine platform" as MINUS is only valid for Oracle. The equivalent for SQL Server is EXCEPT, and I don't know if MySQL has an equivalent or if is supports EXISTS(). In any case, even if you find a syntax they all support, none of them will optimize or process the SQL the same way, and performance will vary.By the way, the LEFT JOIN syntax would be better classified as a "conventional JOIN", as it is supported by the ISO SQL standard:SELECT D.deptno, D.dnameFROM dept DLEFT JOIN emp E ON D.deptno = E.deptnoWHERE E.deptno IS NULLORDER BY D.deptno; That is somewhat more likely to work on all 3 platforms, and actually uses JOIN syntax. They still won't process the same way, although their optimizers may construct the same or very similar plan to any of the other forms you listed. |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2012-11-07 : 07:09:05
|
quote: which query (Anti JOIN or JOIN) has a faster performance regradless of SQL engine platform (Orcle, SQL server, Mysql)?
It's easy to forget that when we talk about set based code from a logical principal -- that underneath the hood there is no set based operation going on.Any engine on any classical computer will need to break down into loops of code at some point. So your question isn't really answerable.That leaves us with style and statement:For me a LEFT JOIN and a WHERE XX IS NULL construction isn't really stating what you want (its not being declarative)Both EXCEPT and NOT EXISTS are more declarative. EXCEPT is the iso standard (oracle uses MINUS)but if you want to talk about performance you need to talk in terms of engines and implementations.Transact CharlieMsg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. http://nosqlsolution.blogspot.co.uk/ |
|
|
|
|
|
|
|