How to compare columns in two different tables in SQL

Here we are going to see how we can compare the columns of two different tables in SQL. We will be taking a few examples to see how we can do this in different ways.

Overview :
In this, we will understand overview of SQL query for required operation to perform How to compare columns in two different tables in SQL. We will understand each concept with the help of examples. 

Step-1: Creating database :
To create the database use the following SQL query as follows.

Syntax  –

create database_name;

Example –

create STUDENTS_DATA

Step-2: Using the database :
To use this database as follows.

Syntax –

use database_name;

Example –

use STUDENT_DATA

Step-3: Creating table :
Creating Table for making queries as follows.

Table-1: Syntax –

create table table_name
(
column1 type, 
column2 type, 
... 
);

Example –

create table studentData1 
(
roll_number int primary key, 
firstname varchar(100), 
lastname varchar(100), 
marks int
);

Step-4: Inserting records :
Inserting records in table studentData1 as follows.
Syntax –

insert into table_name(column1, column2 ...) values (value1, value2 ...);

Inserting into studentData1 table –

insert into studentData1 (roll_number, firstname, lastname, marks) 
values (1, 'albert', 'einstein',356);
insert into studentData1 (roll_number, firstname, lastname, marks) 
values (2, 'isaac', 'newton',412);
insert into studentData1 (roll_number, firstname, lastname, marks) 
values (3, 'marie', 'curie',436);
insert into studentData1 (roll_number, firstname, lastname, marks) 
values (4, 'philip', 'jsam',389);
insert into studentData1 (roll_number, firstname, lastname, marks) 
values (5, 'tom', 'jsam',452);
insert into studentData1 (roll_number, firstname, lastname, marks) 
values (6, 'tucker', 'jose',412);
insert into studentData1 (roll_number, firstname, lastname, marks) 
values (7, 'drawn', 'caste',389);

Step-5: Creating table2 :
Here, we will create the studentData2 using SQL query as follows.

create table studentData2 
(
id int primary key, 
firstname varchar(100), 
lastname varchar(100), 
marks int
);

Step-6: Inserting records :
Inserting records in the table studentData2 as follows.

insert into studentData2 (id, firstname, lastname, marks) 
values (2, 'isaac', 'newton',412);
insert into studentData2 (id, firstname, lastname, marks) 
values (3, 'marie', 'curie',436);
insert into studentData2 (id, firstname, lastname, marks) 
values (6, 'tucker', 'jose',412);
insert into studentData2 (id, firstname, lastname, marks) 
values (4, 'philip', 'jsam',389);

Example-1 :
Using the where clause to compare columns of two different tables. It cannot handle the null values.
Syntax :
(to select all the records with all columns)

select * from table1, table 
where 
table1.col1 = table2.col2 and table1.col1 > someValue;

Syntax(Alternative) :
(to select specific columns from the tables)

select t1.col1, t2.col2,t3. col3 ...., t2.col1, t2.col2, t2.col3....  
from table1 t1, table t2 where t1.col1 = t2.col2 and t1.col1 <> t2.col2;

Query –

select * from studentData1, studentData2 
where studentData1.roll_number = studentData2.id;

Output :
Result based on the comparison of roll_number and id as follows.

roll_number	firstname	lastname	marks	id
2	isaac	newton	412	2
3	marie	curie	436	3
4	philip	jsam	389	4
6	tucker	jose	412	6

Example-2 :
Using joins to compare columns by priority among the table. For example, left join returns all values from the first table and null value for the not-matched records from the second table. Similarly, we can use right join, inner join, full join and self join as per our requirements. In the below example we have compared two tables based on the columns roll_number and id using the left join.

Syntax –

select t1.col1, t1.col2... , t2.col1, t2.col2... , 
from table1 as t1 left 
join table2 as t2 on 
tabe1.col1 = table2.col1;

Query –

select a.roll_number, a.firstname, b.id 
from studentData1 as a left 
join 
studentData2 as b on 
a.roll_number = b.id;

Output :
Join based on left table i.e. sutdentData1 as follows.

roll_number	firstname	id
1	albert
2	isaac	2
3	marie	3
4	philip	4
5	tom
6	tucker	6
7	drawn

Example-3 :
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. It can quickly check what are the data missing or changed in either table. It is capable of handling null values which cannot be handled by where clause.

Note –
This is only used when we have the same type of tables.

Syntax –

select col1, col2, col3 ....  
from (select col1, col2, col3 ... 
from Table1 union all select col1, col2, col3 ... 
from Table2) 
cmpr order by ID;

Example –

select * from 
(select * from studentData1 
where roll_number > 4 
union all 
select * from studentData2 where id < 6) 
cmpr order by marks;

Output :
Compared same records where roll_number > 4 and id < 6 as follows.

roll_number	firstname	lastname	marks
7	drawn	caste	389
4	philip	jsam	389
6	tucker	jose	412
3	marie	curie	436
5	tom	jsam	452