"Erwin Leonardi" <er***@theleonardi.com> wrote in message
news:32**************************@posting.google.c om...
Hi all,
I just start using SQL Server for my project. I have some questions
related to set operations. Suppose I have two tables, Table A and
Table B, as following.
TableA TableB
======= =======
--------------- ---------------
| ID | DATA | | ID | DATA |
--------------- ---------------
| 1 | Val-01 | | 1 | Val-01 |
| 2 | Val-01 | | 2 | Val-02 |
| 3 | Val-02 | | 3 | Val-02 |
| 4 | Val-03 | | 4 | Val-03 |
| 5 | Val-04 | ---------------
| 6 | Val-05 |
---------------
In DB2, I can write SQL statements as following
SQL 1:
===========================
SELECT DATA FROM TableA
EXCEPT
SELECT DATA FROM TableB
And the result will be
Val-04
Val-05
===========================
SQL 2:
===========================
SELECT DATA FROM TableA
EXCEPT ALL
SELECT DATA FROM TableB
And the result will be
Val-01
Val-04
Val-05
===========================
1. How can I handle the EXCEPT (ALL) operator in SQL Server?
2. Are there equivalent SQL queries for the above SQL queries?
Thank you
Erwin Leonardi
EXCEPT can be handled, as Erland pointed out, with a NOT EXISTS subquery.
EXCEPT ALL can be handled by creating a table of natural numbers.
CREATE VIEW Digits (d)
AS
SELECT 0
UNION ALL
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5
UNION ALL
SELECT 6
UNION ALL
SELECT 7
UNION ALL
SELECT 8
UNION ALL
SELECT 9
CREATE VIEW NaturalNumbers (n)
AS
SELECT Ones.d + 10 * Tens.d + 100 * Hundreds.d + 1
FROM Digits AS Ones
CROSS JOIN
Digits AS Tens
CROSS JOIN
Digits AS Hundreds
CREATE TABLE A
(
col1 INT NOT NULL
)
INSERT INTO A (col1)
VALUES (1)
INSERT INTO A (col1)
VALUES (2)
INSERT INTO A (col1)
VALUES (2)
INSERT INTO A (col1)
VALUES (2)
INSERT INTO A (col1)
VALUES (2)
CREATE TABLE B
(
col1 INT NOT NULL
)
INSERT INTO B (col1)
VALUES (2)
INSERT INTO B (col1)
VALUES (3)
-- SELECT col1 FROM A EXCEPT ALL SELECT col1 FROM B
SELECT A.col1
FROM (SELECT A.col1, A.tally - COALESCE(B.tally, 0) AS tally
FROM (SELECT col1, COUNT(*) AS tally
FROM A
GROUP BY col1) AS A
LEFT OUTER JOIN
(SELECT col1, COUNT(*) AS tally
FROM B
GROUP BY col1) AS B
ON A.col1 = B.col1
WHERE B.tally IS NULL OR B.tally < A.tally) AS A
INNER JOIN
NaturalNumbers AS N
ON N.n <= A.tally
ORDER BY A.col1
col1
1
2
2
2
--
JAG
