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

To perform a SQL JOIN on multiple columns in the same table, we use the Self Join. This technique allows us to create connections between different columns of the same table by comparing them directly. We can implement a Self Join using various types of joins such as “inner,” “left,” “right,” “full,” or “cartesian” join.

This article will explore how to effectively use a Self Join to connect multiple columns within the same table, making it easier to analyze complex data relationships.

Performing SQL Join on Multiple Columns

This section demonstrates the process of joining multiple columns in a single table using Self Join. It includes the creation of a table, data insertion, and queries to retrieve and analyze employee information across departments and locations.

Step 3: Create a Table.

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)
);

Step 5: Insert Data into the Table

This step involves inserting sample data into the CORPORATE table. The data represents employees who may work in multiple departments or locations, setting up the conditions to demonstrate Self Join later.

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');

Step 6: Display All Rows of the Table

To verify the inserted data, use the SELECT query to fetch all rows. This ensures the data is correctly populated in the CORPORATE table.

Query:

SELECT * 
FROM CORPORATE;

Output

Step 7: Perform Self Join to Retrieve Specific Employee Details

Retrieve the details of all the employees who have worked in at least 2 departments and at least 2 locations. 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

Explanation:

• The result shows that VARUN has worked in two different departments and two different locations. RAM and RAVI are not displayed because they do not meet both criteria.
  
• 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: Display Distinct Employee Names

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. Only VARUN appears in the result as he meets the criteria of working in different departments and locations

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

Points to remember

• A self join is used to join a table to itself, allowing for comparison within the same table.
• Self joins can utilize inner, left, right, full, or cartesian joins.
• Use table aliases to differentiate between the instances of the table in the join.

Conclusion

In this article, we have covered how to join multiple columns in a table using SQL, specifically focusing on the Self Join method. By understanding how to use the LIKE operator and the Self Join technique, we can effectively filter data based on patterns and analyze relationships between the same table’s columns. This is a crucial skill for SQL beginners and professionals working with complex data sets, as it allows for more advanced queries and better data management practices.