Open In App

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

Improve
Improve
Like Article
Like
Save
Share
Report

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:


Last Updated : 14 Nov, 2021
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads