Aram Koukia (12/9/2013)
gary.morey (12/9/2013)
I have 2 tables. Each one has the following columns: SSN, PKT and FICE_NBR.I need to be able to retrieve the rows from table A where the combo of SSN and PKT and FICE_NBR in table A do not equal the combo of SSN and PKT and FICE_NBR in table B. Each of the tables has approx. 4 million rows.
I need to be able to include all of the columns from table A in the result set. The columns from table B are not required in the result set.
Any thoughts on the best way to write the SQL for this query?
Any help would be appreciated.
you can go like this:
SELECT SSN , PKT , FICE_NBR
FROM A
WHERE SSN + PKT + FICE_NBR
NOT IN (
SELECT SSN + PKT + FICE_NBR
FROM B
)
and for the duplicates a DISTINCT is enough i think
like:
SELECT DISTINCT SSN , PKT , FICE_NBR
FROM BLAH ...
1. A row in table a with SSN = 1 and PKT = 2 would match a row in table b with SSN = 2 and PKT = 1 and matching FICE_NBR. This method is not recommended where the matching columns can generate an arithmetic addition.
It can break with character data too, less likely if an unexpected character e.g. '|' separates the columns.
2. It's not SARGable. In the unlikely event that it works, performance will always be poor.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden