CODEWITHSUNDEEP
sql-server
TheShadow
TheShadow

Reputation: 53

Comparing SQL tables

I am still new in SQL. I am currently having two tables in SQL server and I would like to not exactly compare but more likely see if the one specific column in table 1 is equal to similar specific column in table 2. I have a certain level of success with it but I would like to see also the ones which don't match from table 1 with table 2 (e.g. it can give back null value). Below you can see an example code which might help to understand better my point:

select tb1.models, tb1.year, tb1.series, tb2.model, tb.price
from tb1, tb2
where tb1.year = '2014' and tb1.models = tb2.model

and here comes the place which I have tried all kind of combinations like <> and etc. but unfortunately haven't got to a solution. The point is that in table 1 I have certain amount of models and on table 2 I have quite huge list which sometimes is not including the same ones from table 1. Due to which I want to see what is not matching exactly so I can try to check and analyse it.

The above example I've shown is returning only the ones which are equal and I see for example that there are 30 more models in table 1 but they are not in table 2 and don't have visibility which ones exactly.

Thank you in advance!

Upvotes: 1

Views: 50

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

Btw: Do not use '2014', if this value (and the column tb1.year) is numeric (probably INT). Rather use tb1.year=2014. Implicit casts are expensive and can have various side effects...

This sounds like a plain join:

select tb1.models
     , tb1.year
     , tb1.series
     , tb2.model
     , tb.price
from tb1
INNER JOIN tb2 ON tb1.models = tb2.model
where tb1.year = '2014'

But your model*s* vs. modell might point to troubles with not normalized data... If this does not help, please provide sample data and expected output!

UPDATE

  • Use LEFT JOIN to find all rows from tb1 (rows without a corresponding row in tb2 get NULLs
  • USE RIGHT JOIN for the opposite
  • USE FULL OUTER JOIN to enforce all rows of both tables with NULLs on both sides, if there is no corresponding row.

Upvotes: 1

Related Questions