How to avoid using operator when there are different values on two tables ?

Question

Post reply

How to avoid using operator when there are different values on two tables ?

ahmed_elbarbary.2010

Hall of Fame

Points: 3118

June 17, 2022 at 6:36 pm

Go to Answer

#4051130

I work on sql server 2017 I need to get different of feature value based on partid and feature name

I search for alternative way to compare different values based on partid and feature name without using <> not equal operator

because i face slow when using <> not equal so what i do

so i try below

create table #parts
 (
 PartId int,
 FeatureName varchar(50),
 FeatureValue varchar(50) 
 )
 insert into #parts(PartId,FeatureName,FeatureValue)
 values
 (1211,'Height',50),
 (3211,'Air',90),
 (6121,'Size',300),
 (7921,'Area',790),
 (9871,'Factors',210)
    
    
 create table #partsDetails
 (
 PartId int,
 FeatureName varchar(50),
 FeatureValue varchar(50) 
 )
 insert into #partsDetails(PartId,FeatureName,FeatureValue)
 values
 (1211,'Height',120),
 (3211,'Air',90),
 (6121,'Size',200),
 (7921,'Area',790),
 (9871,'Factors',410)
    
 select p.* from #parts p
 inner join #partsDetails d on p.partid=d.partid and p.featurename=d.featurename and p.featurevalue<>d.featurevalue

expected result

i have 10 milion row on table #parts

and 15 milion rows on table #partsDetails

so if there are another way then i will check it

Jeffrey Williams

SSC Guru

Points: 90254
More actions
June 17, 2022 at 7:54 pm
Answer
#4051144
It looks like you want the results from the first table when they don't match. For this specific case I would use EXCEPT instead of joining and comparing:
```
  Select * From #parts p
  Except
  Select * From #partsDetails pd;
```
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 23, 2022 at 12:53 pm

This was removed by the editor as SPAM

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

ratbak Ten Centuries Points: 1333 More actions · Answer 1

You have no indexes, so you are forcing the query engine to scan the entire tables.

Yes, not equals/not in/not exists can be less efficient than equals/in/exists, but with proper indexing they can be efficient.

How many rows do you have where FeatureValue differs?

Your scenario seems kind of arbitrary. Why would #partsDetails table have exactly the same columns as #parts. It seems odd that given you are querying for differences, you are not actually returning the differing #partsDetails.FeatureValue column in your select.

It's also odd, though not inconceivable, that FeatureValue would differ, unless #parts.FeatureValue actually represents a default or standard value, while #partsDetails.FeatureValue represents an implemented value. But in that case, I would suggest the column in #parts should be named more specifically (e.g., FeatureValueDefault) to identify what it actually represents.

Jeff Moden SSC Guru Points: 1003872 More actions · Answer 2

Jeffrey Williams wrote:

It looks like you want the results from the first table when they don't match. For this specific case I would use EXCEPT instead of joining and comparing:
  Select * From #parts p
  Except
  Select * From #partsDetails pd;

That's the ticket, IMHO. It'll even do NULL comparisons that way.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

carsten.saastamoinen Old Hand Points: 340 More actions · Answer 3

But the except version gives a different result than join, if the table Parts have rows - PartID and FeatureName - that does not exist in PartsDetails. And from the definition of the tables, one of the solutions must de wrong.

This reply was modified 1 year, 10 months ago by carsten.saastamoinen.

Kingfossil SSC Journeyman Points: 78 More actions · Answer 4

In this context, the <> isn't really a problem. The issue with <> in joins is typically that you have to compare every record in the second table to the current record in the primary table to see what doesn't match.

With the right indexing applied, that won't be an issue here. For every record in #Parts, you will only have to read one row in #PartsDetail - the one that matches on PartID and FeatureID. That will happen with a SEEK PREDICATE. From there, a PREDICATE will compare FeatureValue from the two tables and decide what rows make it into the final result set.

You'll need to make sure #Parts and #PartsDetail have a unique index on PartID and FeatureID, with FeatureValue as an included column. With the exact table specifications you give above, I'd actually use a clustered primary key on (partid, featureid).

jimmyblack21 Newbie Points: 1 More actions · Answer 5

To compare data by using the New Data Comparison Wizard

On the SQL menu, point to Data Compare, and then click New Data Comparison

Identify the source and target databases

Select the check boxes for the tables and views that you want to compare.

Jeff Moden SSC Guru Points: 1003872 More actions · Answer 6

jemmielennister wrote:

Using joins to compare columns by priority among the table. For example, left join returns all values from the first table and null value for the not-matched records from the second table. Similarly, we can use right join, inner join, full join and self join as per our requirements.

Smells like a precursor to SPAM. You copied a paragraph from Example 2 of the article at the following link. Or should I say your spam-testing-bot did?

https://www.geeksforgeeks.org/how-to-compare-columns-in-two-different-tables-in-sql/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003872 More actions · Answer 7

jimmyblack21 wrote:

To compare data by using the New Data Comparison Wizard On the SQL menu, point to Data Compare, and then click New Data Comparison Identify the source and target databases Select the check boxes for the tables and views that you want to compare.

Yeah... you're another one. Here's the link that you plagiarized...

https://docs.microsoft.com/en-us/sql/ssdt/how-to-compare-and-synchronize-the-data-of-two-databases?view=sql-server-ver16

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)