SQL how to compare two columns from two different tables

8
votes

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?

7
mysqlsqlsql-server
Do you want to select all rows from t1 where t1.column1 <> t2.column6 and t1.column2 <> t2.column7? - Márcio Gonzalez
Are you using SQL Server and/or MySQL? (Don't tag different dbms products not used!) - jarlh
Give some example data to demonstrate the behaviour you need, I can interpret your post in several different ways. - MatBailie

7 Answers

2
votes

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
2
votes

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
1
votes

NOT EXISTS is a "null safe" version of NOT IN. If you mean the combination column1 AND column2 not in same row in table2:

select *
from table1
where NOT EXISTS (select 1 from table2
                  where table1.column1 = table2.column6
                    and table1.column2 = table2.column7)

Or if you mean just column1 and column2 values can't even be in different rows in table2:

select *
from table1
where NOT EXISTS (select 1 from table2
                  where table1.column1 = table2.column6)
  and NOT EXISTS (select 1 from table2
                  where table1.column2 = table2.column7)
0
votes

The query with the least comparisions I can think of is

Select t1.* 
from table1 t1
left join table2 t2 on t1.column1 in (t2.column6, t2.column7)
                    or t1.column2 in (t2.column6, t2.column7)
where t2.column6 is null
0
votes
    select * from table1 where column1 not in(select column 6 from table2) or column2 not in(select column7 from table2)

This will give you rows from table1 where there are differences between col1 and col6 or col2 and col7

Hope this helps

0
votes
SELECT *  FROM table1 t1
RIGHT JOIN table2 t2
WHERE
t1.c1 = t2.c6 AND
t1.c2 = t2.c7
0
votes

Please try this query:

 Select 
     case when (table1.column1 = table2.column6) 
         then 1 else 0 
     end column1_6 check, 
     case when (table1.column2 = table2.column7) 
         then 1 else 0 
     end
 from 
     table1 
 inner join
     table2 on table1.ID = Table2.ID