July 21, 2009 at 3:35 am
The ANY and ALL operators are seriously error prone, because in English we can use 'any' in place of 'every', which could lead to the (incorrect) use of >ANY instead of >ALL. So avoid using ANY of them at ALL.
July 21, 2009 at 4:27 am
Hi David,
I am new to sql server, could you please let me know how you calculated the total execution cost of the queries.
Regards.
July 21, 2009 at 5:25 am
In the graphical execution plan if you move the mouse pointer over the left hand graphic the costs are revealled.
July 21, 2009 at 5:32 am
Hi David,
by looking at the execution plan of below query could you please let me know how you calculated the overall query cost as 0.0517279.
SELECT C.CustomerID
FROM Sales.Customer AS C
LEFT JOIN Sales.SalesOrderHeader AS OH
ON C.CustomerID = OH.CustomerID
AND OrderDate>='2004-07-01'
WHERE OH.CustomerID IS NULL
AND C.TerritoryID=10
July 21, 2009 at 7:42 am
What about UNION? You showed what it does but you did not show any examples.
July 21, 2009 at 7:46 am
I didn't include UNION because I could not think of an alternative to the UNION statement that didn't involve creating temporary tables.
INTERSECT and EXCEPT can be modelled with joins and IN / NOT IN statements.
UNION statements have been around for donkey's years.
July 21, 2009 at 12:22 pm
My default browser has to be IE here at work, so when I selected this report it was opened in IE. As mentioned by others, the examples were very small blocks with no way to expand them.
So I copied the URL into my favorite browser - Safari - and everything displayed correctly. I highly recommend Safari on Windows. It is a rare site that is so oriented to IE that I have to switch to IE.
July 21, 2009 at 5:29 pm
The image problem is not the fault of the browser. The site seems to have had a problem with some images. It lost my avatar, for example.
I use Firefox because of its built in spell checker. Many people have favorite browsers. That's fine and I know we all won't agree on the "best" one. We can't even agree on the "best" dialect of SQL!
Even so I'll give Safari a look
ATBCharles Kincaid
July 22, 2009 at 12:23 am
Hi David,
by looking at the execution plan of below query could you please let me know how you calculated the overall query cost as 0.0517279.
SELECT C.CustomerID
FROM Sales.Customer AS C
LEFT JOIN Sales.SalesOrderHeader AS OH
ON C.CustomerID = OH.CustomerID
AND OrderDate>='2004-07-01'
WHERE OH.CustomerID IS NULL
AND C.TerritoryID=10
July 22, 2009 at 3:19 am
Jeff Moden (7/19/2009)
Why would anyone think that INTERSECT is easier than INNER JOIN?
Another reason other than a large number of columns is if you are using two result sets that are the result of more complicated queries, they could be wrote as an inner join using:
SELECT ... FROM ( SELECT ... ) INNER JOIN ( SELECT ... )
but in this case it can be simpler to read if you just write each query separately and combine them using an INTERSECT instead.
July 22, 2009 at 7:31 am
Using where not exists will in most cases be faster then using a 'left join .. is null' to do an 'except'.
Example: let's say you need the ItemCode from Item table for Items that do not belong in the Demand table.
1. select ItemCode from dbo.Item i left join dbo.Demand d on i.ItemID=d.ItemID where d.ItemID is null
2. select ItemCode from dbo.Item i where not exists(select * from dbo.Demand d where i.itemID=d.itemID)
the not exists will use less rows from Demand table then the left join approach.
Seen queries that ran 20s to go to 0s.
greets.
July 27, 2009 at 9:08 am
EXCEPT and INTERSECT are especially useful for comparing multiple columns of data where (a) there is no primary key or (b) having the same primary key does not guarantee that the associated data is the same.
Typical scenario is regression testing:
select * from OldTable
intersect
select * from NewTable
If this gives you the same number of rows as OldTable and NewTable, you know your new process is producing the same data as the old process. Use EXCEPT to examine the rows with differences. If you list the columns, you can narrow down the location of your differences by commenting out various columns.
The alternatives for this scenario are (a) joining OldTable to NewTable on every single column, or (b) dumping the data to text files and comparing them.
July 27, 2009 at 9:29 am
phickey (7/27/2009)
EXCEPT and INTERSECT are especially useful for comparing multiple columns of data where (a) there is no primary key or (b) having the same primary key does not guarantee that the associated data is the same.Typical scenario is regression testing:
select * from OldTable
intersect
select * from NewTable
If this gives you the same number of rows as OldTable and NewTable, you know your new process is producing the same data as the old process. Use EXCEPT to examine the rows with differences. If you list the columns, you can narrow down the location of your differences by commenting out various columns.
The alternatives for this scenario are (a) joining OldTable to NewTable on every single column, or (b) dumping the data to text files and comparing them.
Cool trick but if you have two identical tables with identical data, you have much bigger problems. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
July 27, 2009 at 11:05 am
Interesting article, but I don't think your examples really capture what the functionality was intended to provide.
They are set operations (nicely illustrated in your Venn diagrams at the top), they are not there to replace joins.
So use them in scenarios where you have matching multi-column result sets (like you would with UNION); rather than in scenarios where you have a single value (in which case clearly a join/in is simpler).
For example, EXCEPT provides a pretty simple way to compare data in 2 big tables with lots of columns ...
/* Below query will yield all the rows that are in Table1 but not in Table2 - matching on each column - assuming the structure of the tables are them same */
SELECT * FROM Table1
EXCEPT
SELECT * FROM Table2
/* Below query will yield all the rows that are in Table2 but not exactly the same in Table1 - matching on each column - assuming the structure of the tables are the same */
SELECT * FROM Table2
EXCEPT
SELECT * FROM Table1
Running both queries will tell you all the rows that are new/missing/different between the 2 tables.
... if you want to omit certain columns from the 'match' criteria, or the tables have different columns, vary the select lists to be a named list of column rather than '*'. Obviously you have to be careful with duplicates rows ...
... similarly you can have WHERE clauses so you can match rows on the same table.
July 27, 2009 at 8:30 pm
Stephen Worthington (7/27/2009)
Interesting article, but I don't think your examples really capture what the functionality was intended to provide.They are set operations (nicely illustrated in your Venn diagrams at the top), they are not there to replace joins.
Heh... thank you. The voice of reason finally bubbles to the surface. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 31 through 45 (of 86 total)
You must be logged in to reply to this topic. Login to reply