Comparing two columns for equality in a SQL query is a fundamental operation used in various data analysis tasks. This involves checking if the values in corresponding rows of two columns are the same. This article demonstrates how to achieve this comparison using the equal to operator (=) in SQL, with examples using Microsoft SQL Server.
Comparing Columns with the Equal To Operator
The simplest way to compare two columns for equality is using the = operator within the WHERE clause of your SQL query.
SELECT *
FROM TABLE_NAME
WHERE COLUMN1_NAME = COLUMN2_NAME;This query retrieves all rows from the table where the values in COLUMN1_NAME and COLUMN2_NAME are identical.
Practical Example: Professor Promotion
Let’s illustrate this with a scenario. Imagine a table named COLLEGE storing information about professors, including their name (PROF_NAME), the subject they currently teach (CURR_SUBJ), and their specialization subject (SPEC_SUBJ). The college director wants to promote professors to dean if their current subject matches their specialization.
1. Create the Table:
CREATE TABLE COLLEGE (
PROF_NAME VARCHAR(20),
CURR_SUBJ VARCHAR(20),
SPEC_SUBJ VARCHAR(20)
);2. Populate the Table:
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');3. Compare and Select:
Now, to find professors eligible for promotion, we compare CURR_SUBJ and SPEC_SUBJ:
SELECT *
FROM COLLEGE
WHERE CURR_SUBJ = SPEC_SUBJ;This query returns:
| PROF_NAME | CURR_SUBJ | SPEC_SUBJ |
|---|---|---|
| ABHISHEK | SOFTWARE | SOFTWARE |
| RAKESH | ORGANIC | ORGANIC |
This result shows that Abhishek and Rakesh are eligible for promotion as their current subject and specialization match.
SQL Query Result
Conclusion
Comparing two columns for equality in SQL is straightforward using the = operator. This technique provides a powerful way to filter and analyze data based on matching values within a table, enabling you to extract meaningful information for decision-making. The example of professor promotions demonstrates a practical application of this comparison in a real-world scenario.
