Menu
I have two tables, in which table 1 contains 4 columns while table 2 contains 8 columns. I have two columns in table1 that I want to compare them with two columns in table2.
Table 1 have column1 and column2 (that needs to be compared)
Table 2 have column6 and column7 (that needs to be compared)
I need to compare the combination of the two columns. I tried to do the below query however it doesn't work
Select * from table1
where column1, column2 NOT IN (Select column6, column7 from table2)
How can I compare the two columns in the the two tables?
804
select a.roll_number, a.firstname, b.id from studentData1 as a left join studentData2 as b on a.roll_number = b.id; Join based on left table i.e. sutdentData1 as follows. UNION allows us to compare two same types of tables or datasets. We can use union to compare the columns once we can have the union of both the tables.
In your case, since you want to select from two different databases, you have to use a fully qualified table names. They have to be in the form database.schema.object_name. Update: If you want compare the two tables columns' names, not the data itself, you have to work with the metadata tables to compare the columns' names the same way with EXCEPT.
If you are looking to compare two tables and find matched records based on multiple columns, then here’s SQL query. Let’s say you want to find identical records by comparing multiple columns id, order_date, amount
Source: Use NATURAL FULL JOIN to compare two tables in SQL by Lukas Eder Clever approach of using NATURAL FULL JOIN to detect the same/different rows between two tables. SELECT t1.*, t2.*, CASE WHEN t1 IS NULL OR t2 IS NULL THEN 'Not equal' ELSE 'Equal' END FROM t1 NATURAL FULL JOIN t2; SELECT * FROM (SELECT 't1' AS t1, t1.*
Except shows the difference between two tables (the Oracle guys use minus instead of except and the syntax and use is the same). It is used to compare the differences between two tables. For example, let's see the differences between the two tables
SELECT * FROM
table1
EXCEPT
SELECT * FROM
table2
Try a minus statement. This will give you any results from the first select statement of table1 that aren't in the second select statement on table2.
select column1, column2 from table1
minus
select column6, column7 from table2
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
