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 w3wiki.
Query:
CREATE DATABASE w3wiki
Output:
Step 2: Use the w3wiki database. For this use the below command.
Query:
USE w3wiki
Output:
Step 3: Create a table CORPORATE inside the database w3wiki. 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: