How to Compare Two Columns For Equality in SQL Server?

In SQL, problems require us to compare two columns for equality to achieve certain desired results. This can be achieved through the use of the =(equal to) operator between 2 columns names to be compared. For this article, we will be using the Microsoft SQL Server as our database.

Syntax: 

SELECT * FROM TABLE_NAME WHERE COLUMN1_NAME=COLUMN2_NAME;

There is a table called COLLEGE. The table consists of professor name, the subject that the professor is teaching currently and the subject in which the professor specializes. Then director of the college decides to promote some of the professors to the post of dean but on the condition that their current subject should match with the specialization subject of the professor. Create a table and write an SQL query to demonstrate this.

Step 1: Create a Database. For this use the below command to create a database named GeeksForGeeks.

Query:

CREATE DATABASE GeeksForGeeks

Output:

Step 2: Use the GeeksForGeeks database. For this use the below command.

Query:

USE GeeksForGeeks

Output:

Step 3: Create a table COLLEGE inside the database GeeksForGeeks. This table has 3 columns namely PROF_NAME, CURR_SUBJ and SPEC_SUBJ containing professor name, current subject that he/she is teaching and the subject in which he/she specializes.

Query:

CREATE TABLE COLLEGE(
PROF_NAME VARCHAR(20),
CURR_SUBJ VARCHAR(20),
SPEC_SUBJ VARCHAR(20));

Output:

Step 4: Display the structure of the COLLEGE table.

Query:

EXEC SP_COLUMNS COLLEGE;

Output:

Step 5: Insert 5 rows into the COLLEGE table.

Query:

INSERT INTO COLLEGE VALUES('BHARGAV','ELECTRO','FLUIDS');
INSERT INTO COLLEGE VALUES('ABHISHEK','SOFTWARE','SOFTWARE');
INSERT INTO COLLEGE VALUES('SUDHARSHAN','TRANSFORMERS','CIRCUITS');
INSERT INTO COLLEGE VALUES('RAKESH','ORGANIC','ORGANIC');
INSERT INTO COLLEGE VALUES('DEEPAK','OOPS','ALGORITHMS');

Output:

Step 6: Display all the rows of the COLLEGE table.

Query:

SELECT * FROM COLLEGE;

Output:

Step 7: Display the details of the professor who can be promoted to the position of the dean i.e. the current subject should match the specialization subject.

Query:

SELECT * FROM COLLEGE WHERE CURR_SUBJ=SPEC_SUBJ;

Output:

Thus, in the above-stated ways, we can compare any two columns for equality in SQL as and when needed.