Menu
I have two tables TableA and TableB which have the same format of columns, which means both tables have the columns
A B C D E F where A and B are the primary keys.
How do I write an SQL query to check if TableA and TableB (which have identical primary keys) contain exactly the same values in every column?
It means that these two tables have exactly the same data.
924
If you are using SQL Server 2005, then you can use Intersect Key word, which gives you common records. If you want in the output both column1 and column2 from table1 which has common columns1 in both tables.
The SQL intersect operator allows us to get common values between two tables or views. The following graphic shows what the intersect does. The set theory clearly explains what an intersect does. In mathematics, the intersection of A and B (A ∩ B) is the set that contains all elements of A that also belong to B.
Use the INNER JOIN function to find duplicates that exist in multiple tables. Sample syntax for an INNER JOIN function looks like this: SELECT column_name FROM table1 INNER JOIN table2 ON table1. column_name = table2.
You should be able to "MINUS" or "EXCEPT" depending on the flavor of SQL used by your DBMS.
select * from tableA minus select * from tableB If the query returns no rows then the data is exactly the same.
110
Using relational operators:
SELECT * FROM TableA UNION SELECT * FROM TableB EXCEPT SELECT * FROM TableA INTERSECT SELECT * FROM TableB; Change EXCEPT to MINUS for Oracle.
Slightly picky point: the above relies on operator precedence, which according to the SQL Standard is implementation dependent, so YMMV. It works for SQL Server, for which the precedence is:
INTERSECTEXCEPT and UNION evaluated from left to right.
26
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
