Minus Function

[lyudmila]

Is Minus legal function in T-SQL?

select * from tableA
minus
select * from tableB

Table A has 1,000,000 records
Table B has 15,000,000 records
I have three key fileds for comparison, but if I do:

select * from tableA
minus
select * from tableB
where TableA.col1=TableB.Col1
and TableA.col2=Tableb. col2




I'm testing, it works fine, but I never used this function. Any input greatly appreciate.

 

[MeanGreen]

Minus is not a function within MS SQL as far as I can tell. I ran your query and looked at the execution plan to see that all that is happening is minus becomes the alias for tableA.
For the Query:
select * from tableA
minus
select * from tableB
All your query is doing is assigning an alias of "minus" to TABLEA.
I am not sure how you got the 2nd Query to work since tableA is now know as minus.

Hope this helps.

 

[foxdev]

What result are you looking for? All of the rows in one that are not in the other? If so, you could use a subquery or an outer join. --------------

http://www.lowcostprints.com

 

[lyudmila]

I run this query to find records, which exist in both tables

drop table aphistmatch
Select * into aphistmatch
From aphist10 a
Where Exists
(Select * From aphist_all b
Where a.aiinv=b.aiinv
And a.aidtiv=b.aidtiv
and a.ainum=b.ainum)


When I test result, by running query on both tables and look for identical records in both tables, where aiinv='Value' and aidtiv='Value1', both (aphist10 and aphist_all) tables have these records.

But now I need records from aphist10, which not exist in aphist_all (they are new records, not exist in old table aphist_all) and I run thid proc:


drop table aphistmatchN
Select * into aphistmatchN
From aphist10 a
Where not Exists
(Select * From aphist_all b
Where a.aiinv=b.aiinv
And a.aidtiv=b.aidtiv
and a.ainum=b.ainum)

And after I got result and run query on both table, taking value for aiinv='Value' and value for aidtiv='Value1' from result table, both table (aphist10 and aphist_all) do not have these rows of data.
I'm getting frustrated. What I do wrong?

 

[Zathras]

See my post in thread183-429141 for the solution to a similar problem of finding entries in one table that do not exist in another.

 

[MeanGreen]

This should get you all records in aphist10 that do not exist in aphist_all and copy them into aphistmatchN:

Select a.* into aphistmatchN
From aphist10 a left outer join
aphist_all b
on.aiinv=b.aiinv
And a.aidtiv=b.aidtiv
and a.ainum=b.ainum
where b.aiinv is null

Hope this helps.