Buenas tardes Roxana, Hasta donde yo se T-SQL no soporta todas las operaciones de conjunto.
Sin embargo hay forma de reproducir su comportamiento, aquí hay un artículo que explica como hacerlo: http://msdn.microsoft.com/en-us/library/aa175859(SQL.80).aspx Me refiero a esto: EXCEPT EXCEPT (sometimes called MINUS) returns rows that appear in one input but not in the other, so it also eliminates duplicates. Unlike the preceding set operationsfor which U <operation> V produces the same result as V <operation> Uwith EXCEPT, the order in which you specify the set names affects the results. The expected result from U EXCEPT V is {1}, and the result from V EXCEPT U is {2, 3}. The following query shows a T-SQL way of getting the results for U EXCEPT V: SELECT col1, col2 FROM (SELECT DISTINCT 'U' AS setname, col1, col2 FROM U UNION ALL SELECT DISTINCT NULL, col1, col2 FROM V) AS D1 GROUP BY col1, col2 HAVING COUNT(*) = 1 AND MAX(setname) = 'U' The derived table D1 contains distinct rows from each input and a pseudo column called setname, which contains the literal 'U' for U's rows and NULL for V's rows. The code groups the rows from D1 by col1, col2. The COUNT(*) = 1 expression in the HAVING clause ensures that the query returns only the rows that appear in one of the inputs, and MAX(setname) = 'U' ensures that it returns only the rows that appear in U. The trick to using this code is that the MAX() function doesn't account for NULLs; it considers only the rows in D1 that contain 'U'. To get V EXCEPT U, you can revise the preceding query so that it returns NULL in setname for U's rows and 'V' for V's rows. EXCEPT ALL adds an interesting twist to EXCEPT. For U EXCEPT ALL V, if a row appears x times in U and y times in V, it appears the greater of x minus y or 0 times in the result. The expected result from U EXCEPT ALL V is {1, 2, 2}. Because 2 appears four times in U (x=4) and 2 times in V (y=2), it appears twice (4-2) in the result. Similarly, the expected result from V EXCEPT ALL U is {3, 3, 4). The EXCEPT ALL operation eliminates the 2 from the result because the greater of (2-4) and 0 is 0. Furthermore, 3 appears twice in the result because it appears twice in V and doesn't appear in U at all. Listing 6 shows the query that implements U EXCEPT ALL V in T-SQL. The derived table D1 has the positive count of duplicates for U and the negative count for V. D2, another derived table, summarizes U's positive cnt value and V's negative cnt value into the result column dups and makes sure that the query returns the row only if the value of dups is greater than 0. In effect, the value of dups is MAX(x-y, 0). Finally, the code joins the derived table D2 to the Nums auxiliary table, duplicating each row as many times as the value in dups. To get V EXCEPT ALL U, you can modify Listing 6's query by swapping the positive and negative signs of the counts in U and V. Espero que sirva. Mariano Minoli ________________________________ From: Roxana Leituz <[email protected]> To: [email protected] Sent: Saturday, September 5, 2009 6:32:45 PM Subject: [dbms] minus en sql Hola!! alguien me podra decir como se usa el operador minus de oracle en SQL server?? muchas gracias
