CODEWITHSUNDEEP
sqljoin
Varada
Varada

Reputation: 17092

compare two table values

I have 2 tables table A and table B. In table B we have to check if all the column entered is exactly as in table A, means if a row exists in table B then the same row will be there in table A too. also table A may have rows which are not in table B. if there is a row which is not in table A and is there in table B, an alert should be displayed showing which element is extra in table B. Can we do this using join? if so what will be the sql code?

Upvotes: 1

Views: 12092

Answers (3)

Arun Kumar Arjunan
Arun Kumar Arjunan

Reputation: 6857

If you want to compare based on single column, then you can do something like this:

SELECT ID FROM B LEFT JOIN A ON B.ID = A.ID WHERE A.ID IS NULL;

The above query will give you the list of records that are not present in A but in B.

Instead if you want to compare the entire row, you can use the following approach:

SELECT COUNT(*) FROM B;

SELECT COUNT(*) FROM A;

SELECT COUNT(*) FROM (
  SELECT * FROM B UNION SELECT * FROM A
)

If all the queries returns the same count then you can assume that both the tables are exactly equal.

Upvotes: 0

k102
k102

Reputation: 8089

this is the best picture about joins i've ever seen :)

Upvotes: 15

Adriaan Stander
Adriaan Stander

Reputation: 166536

You probably want to have a look at the following article

SQL SERVER – Introduction to JOINs – Basic of JOINs

This should give you a very clear understanding of JOINs in Sql.

From there you should be able to find the solution.

As an example, you would have to look at something like

TABLE1
    Col1
    Col2
    Col3
    Col4

TABLE2
    Col1
    Col2
    Col3
    Col4

--all rows that match
SELECT  *
FROM    TABLE1 t1 INNER JOIN
        TABLE2 t2   ON  t1.Col1 = t2.Col1
                    AND t1.Col2 = t2.Col2
                    ...
                    AND t1.Col3 = t2.Col3

--rows only in TABLE1
SELECT  *
FROM    TABLE1 t1 LEFT JOIN
        TABLE2 t2   ON  t1.Col1 = t2.Col1
                    AND t1.Col2 = t2.Col2
                    ...
                    AND t1.Col3 = t2.Col3
WHERE   t2.Col1 IS NULL

--rows only in TABLE2
SELECT  *
FROM    TABLE1 t2 LEFT JOIN
        TABLE2 t1   ON  t1.Col1 = t2.Col1
                    AND t1.Col2 = t2.Col2
                    ...
                    AND t1.Col3 = t2.Col3
WHERE   t1.Col1 IS NULL

Upvotes: 1

Related Questions