StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Answer in the general case:</p> <pre><code>SELECT * FROM tableA UNION SELECT * FROM tableB EXCEPT SELECT * FROM tableA INTERSECT SELECT * FROM tableB; </code></pre> <hr> <p>Detailed answer:</p> <p>Q. What do the letters SQL stand for? A. Scarcely Qualifies as a Language... ROFL but consider the following serious points:</p> <pre><code>CREATE TABLE MyTable ( ID INTEGER NOT NULL UNIQUE, data_col VARCHAR(10) NOT NULL ); DECLARE @MyTable TABLE ( ID INTEGER NOT NULL UNIQUE, data_col VARCHAR(10) NOT NULL ); </code></pre> <p>Now questions:</p> <p>Q. In language (computer science) terms, is <code>@MyTable</code> a variable? A. Well...</p> <p>Problem 1: can't assign a table value to <code>@MyTable</code> e.g. </p> <pre><code>-- Assignment attempt 1: SET @MyTable = MyTable; -- COMPILE ERROR -- Assignment attempt 2: SET @MyTable = ( VALUES ( 1, NULL ), ( 2, '' ), ( 3, 'Test' ) ); -- COMPILE ERROR </code></pre> <p>Problem 2: can't compare variables e.g.</p> <pre><code>-- Comparison attempt 1: IF ( @MyTable = @MyTable ) BEGIN; PRINT 'Tables are the same.' END; -- COMPILE ERROR -- Comparison 2: IF ( @MyTable = ( VALUES ( 1, NULL ), ( 2, '' ), ( 3, 'Test' ) ) ) BEGIN; PRINT 'Tables are the same.' END; -- COMPILE ERROR </code></pre> <p>...so we must believe that @MyTable is a 'variable' that support neither assignment nor comparison.</p> <p>Q. If <code>@MyTable</code> is a variable, what in language (computer science) terms is <code>MyTable</code>? A. Constrant? Value? Type? Class? Struct? None of the above? </p> <p>...Yes, SQL is a very strange language indeed!</p> <p>Q. What is a relational operator? A. In the relational model, it is an operator that takes two relation values as arguments and returns a relation value as a result.</p> <p>Q. Does SQL support relational operators? A. Not exactly. SQL does have operators that would be familiar a user of a truely relational language (<code>UNION</code>, <code>INTERSECT</code>, <code>EXCEPT</code>, etc). However, SQL supports non-relational features, most notably nulls, duplicate rows and duplicate column names. Therefore, great care is required to ensure the arguments to and results from these operators are equivalent to relations. </p> <p>Q How to compare two tables for equality using SQL's 'relational-style' operators? A Here's one way:</p> <pre><code>SELECT * FROM tableA UNION SELECT * FROM tableB EXCEPT SELECT * FROM tableA INTERSECT SELECT * FROM tableB; </code></pre> <p>Test to demonstrate the above (noting the following are <em>not</em> all relation values but do prove the operators work logically with SQL nulls):</p> <p>Example 1: tables are the same (expect zero rows == PASS):</p> <pre><code>WITH tableA AS ( SELECT * FROM ( VALUES ( 1, NULL ), ( 2, '' ), ( 3, 'Test' ) ) AS T ( ID, data_col ) ), tableB AS ( SELECT * FROM ( VALUES ( 1, NULL ), ( 2, '' ), ( 3, 'Test' ) ) AS T ( ID, data_col ) ) SELECT * FROM tableA UNION SELECT * FROM tableB EXCEPT SELECT * FROM tableA INTERSECT SELECT * FROM tableB; </code></pre> <p>Example 2: tableB is a proper subset of tableB (expect rows == FAIL):</p> <pre><code>WITH tableA AS ( SELECT * FROM ( VALUES ( 1, NULL ), ( 2, '' ), ( 3, 'Test' ) ) AS T ( ID, data_col ) ), tableB AS ( SELECT * FROM ( VALUES ( 1, NULL ), ( 2, '' ) ) AS T ( ID, data_col ) ) SELECT * FROM tableA UNION SELECT * FROM tableB EXCEPT SELECT * FROM tableA INTERSECT SELECT * FROM tableB; </code></pre> <p>Example 3: tableA is a proper subset of tableB (expect rows == FAIL):</p> <pre><code>WITH tableA AS ( SELECT * FROM ( VALUES ( 1, NULL ), ( 3, 'Test' ) ) AS T ( ID, data_col ) ), tableB AS ( SELECT * FROM ( VALUES ( 1, NULL ), ( 2, '' ), ( 3, 'Test' ) ) AS T ( ID, data_col ) ) SELECT * FROM tableA UNION SELECT * FROM tableB EXCEPT SELECT * FROM tableA INTERSECT SELECT * FROM tableB; </code></pre> <p>Example 4: tableA and tableB have some but not all row values in common (expect rows == FAIL):</p> <pre><code>WITH tableA AS ( SELECT * FROM ( VALUES ( 1, NULL ), ( 4, 'Lone' ) ) AS T ( ID, data_col ) ), tableB AS ( SELECT * FROM ( VALUES ( 1, NULL ), ( 4, 'Sole' ) ) AS T ( ID, data_col ) ) SELECT * FROM tableA UNION SELECT * FROM tableB EXCEPT SELECT * FROM tableA INTERSECT SELECT * FROM tableB; </code></pre> <p>Example 5: tableA and tableB have no row values in common (expect rows == FAIL):</p> <pre><code>WITH tableA AS ( SELECT * FROM ( VALUES ( 5, NULL ), ( 6, 'Different' ) ) AS T ( ID, data_col ) ), tableB AS ( SELECT * FROM ( VALUES ( 7, NULL ), ( 8, 'Not the same' ) ) AS T ( ID, data_col ) ) SELECT * FROM tableA UNION SELECT * FROM tableB EXCEPT SELECT * FROM tableA INTERSECT SELECT * FROM tableB; </code></pre> <p>Q. Why do SQL Server DBAs tend not to use this syntax and prefer, say, <code>FULL OUTER JOIN</code>? A. Probably for a variety of reasons, such as familiarity with legacy syntax (e.g. <code>EXCEPT</code> was introduced in SQL Server 2005). But most likely is that a SQL DBA will tend to want to write what they feel will be the most efficient query (pejoratively, premature optimization). It is indeed true the SQL Server optimizer does not cope well with operators <code>INTERSECT</code> and <code>EXCEPT</code>.</p> <p>Q. Why prefer the 'relational-style' operators? A. Because they are less verbose and arguably easier to read. Both of these are good qualities for test code.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload