CODEWITHSUNDEEP
sqlsql-server
Ratha
Ratha

Reputation: 9692

SQL Server minus query gives issue?

I'm trying to compare two table's values for difference (I suspect for two TankSystemIds containing same data)

My query is

SELECT * 
FROM [dbo].[vwRawSaleTransaction] 
WHERE hdTankSystemId = 2782

MINUS

SELECT *
FROM [dbo].[vwRawSaleTransaction] 
WHERE hdTankSystemId = 2380

But I get an error about syntax issues:

Incorrect syntax near 'minus'

But this is right[1]? [1] https://www.techonthenet.com/sql/minus.php

Upvotes: 1

Views: 874

Answers (1)

lamandy
lamandy

Reputation: 982

Quoted in your link.

For databases such as SQL Server, PostgreSQL, and SQLite, use the EXCEPT operator to perform this type of query.

For your case, it seems like you are looking for duplicated data, intersect should be used instead.

Also, INTERSECT statement like 

SELECT
    EXPRESSION_1, EXPRESSION_2, ..., EXPRESSION_N
FROM
    TABLE_A
INTERSECT
SELECT
    EXPRESSION_1, EXPRESSION_2, ..., EXPRESSION_N
FROM
    TABLE_B

can be written as

SELECT 
    TABLE_A.EXPRESSION_1, TABLE_A.EXPRESSION_2, ..., TABLE_A.EXPRESSION_N
FROM
    TABLE_A
INNER JOIN
    TABLE_B
ON
    TABLE_A.EXPRESSION_1 = TABLE_B.EXPRESSION_1
AND TABLE_A.EXPRESSION_2 = TABLE_B.EXPRESSION_2
.
.
.
AMD TABLE_A.EXPRESSION_N = TABLE_B.EXPRESSION_N

If you use select * from the same table with a different where condition then intersect them, you are not going to get any rows as they have different value on the specific column used in where condition.

Upvotes: 1

Related Questions