sql server (alternate): 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

[quoted text, click to view]


Unfortunately, MS SQL Server doesn't support EXCEPT.

However, recent (this month or lasts) SQL Server Magazine has an article on
how to do this.

Erwin Leonardi (erwin@theleonardi.com) writes:
[quoted text, click to view]

In SQL Server what comes closest at hand is


SELECT Data FROM TableA a
WHERE NOT EXISTS (SELECT *
FROM TableB b
WHERE a.Data = b.Data)

Of course, with many columns in the result set, this becomes somewhat
tedious to write.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at

[quoted text, click to view]

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

[quoted text, click to view]
Another method.

Select a.*
from TableA a
left outer join TableB b
on a.data=b.data