How to Perform SQL Join on Multiple Columns in Same Table?

In SQL, for extracting valuable data, we need to perform self join within the same table. Self-join is a simple cross-product followed by a condition. An illustration of the same is shown in the below article. For this article, we will be using the Microsoft SQL Server as our database.

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 CORPORATE inside the database GeeksForGeeks. This table has 4 columns namely E_NAME, E_ID, E_DEPT, and E_LOC containing the name, id, department, and location of various employees.

Query:

CREATE TABLE CORPORATE(
E_NAME VARCHAR(10),
E_ID INT,
E_DEPT VARCHAR(10),
E_LOC VARCHAR(10));

Output:

Step 4: Describe the structure of the table CORPORATE.

Query:

EXEC SP_COLUMNS CORPORATE;

Output:

Step 5: Insert 5 rows into the CORPORATE table.

Query:

INSERT INTO CORPORATE VALUES('RAM',1,'HR','DELHI');
INSERT INTO CORPORATE VALUES('RAM',1,'SALES','DELHI');
INSERT INTO CORPORATE VALUES('VARUN',2,'IT','BANGALORE');
INSERT INTO CORPORATE VALUES('VARUN',2,'MARKETING','HYDERABAD');
INSERT INTO CORPORATE VALUES('RAVI',3,'FINANCE','KOCHI');
INSERT INTO CORPORATE VALUES('RAVI',3,'FINANCE','TRIVANDRUM');

Output:

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

Query:

SELECT * FROM CORPORATE;

Output:

Step 7: Retrieve the details of all the employees who have worked in at least 2 departments and at least 2 locations.

Note: Use of AS for making 2 aliases of the table CORPORATE with C1 and C2 for comparing the IDs, departments, and locations of the employees.

Query:

SELECT C1.E_NAME,C1.E_ID,C1.E_DEPT,C1.E_LOC FROM
CORPORATE AS C1,CORPORATE AS C2 WHERE C1.E_ID=C2.E_ID 
AND C1.E_DEPT<>C2.E_DEPT AND C1.E_LOC<>C2.E_LOC;

Output:

Note: Here RAM is not displayed although he has worked at 2 different departments as his location was the same. Similarly, RAVI is not displayed although he has worked at 2 different locations as his department was the same.

Step 8: To display just the name(s) of the employees who have worked in at least 2 departments and at least 2 locations, use SELECT just for the E_NAME column and keep that is DISTINCT to avoid redundant rows.

Query:

SELECT DISTINCT(C1.E_NAME) FROM CORPORATE
AS C1,CORPORATE AS C2 WHERE C1.E_ID=C2.E_ID
AND C1.E_DEPT<>C2.E_DEPT AND C1.E_LOC<>C2.E_LOC;

Output: