Solve SQL: Left/Right Join two Tables on a Non-Unique Column

If you are preparing for a Data Analyst or Data Engineer Role, SQL Interviews are a must. The most asked questions are from SQL Joins. Now, usually, we join two tables on some Unique Columns. What happens if we join two tables on some NON-UNIQUE columns. Let’s find out!

Question:

There are 2 tables T1 and T2. None of them has a Primary Key Column. I am doing a Left Outer Join or a Right Outer Join on these 2 tables. What would be the Resultset?

Example:

Now, Let’s take a look at an example:

Table T1:

C1	C2	C3
1	1	Apple
1	2	Grapes
2	2	Mango
2	1	Banana
1	2	Cherry

Table T2:

C1	C2	C3
1	1	Rose
1	2	Lily
2	2	Tulip
2	1	Jasmine
1	2	Daisy

Now, If I do a Left Outer Join on these 2 tables using column C1, what would be the results?

SELECT * FROM T1 
LEFT JOIN T2 On T1.C1 = T2.C1;

Take a moment here and try to guess an output before actually running the below scripts.

Solution:

First, let’s prepare the schema.

Note: To work on this kind of problem, I prefer to use DB-Fiddle. You can run the below scripts on any Database.

## Create Table T1
CREATE TABLE T1(
C1 Integer,
C2 Integer,
C3 Varchar(10));

## Create Table T2
CREATE TABLE T2(
C1 Integer,
C2 Integer,
C3 Varchar(10));

## Insert Data Into T1
INSERT INTO T1 VALUES(1,1,'Apple');
INSERT INTO T1 VALUES(1,2,'Grapes');
INSERT INTO T1 VALUES(2,2,'Mango');
INSERT INTO T1 VALUES(2,1,'Banana');
INSERT INTO T1 VALUES(1,2,'Cherry');

## Insert Data Into T1
INSERT INTO T2 VALUES(1,1,'Rose');
INSERT INTO T2 VALUES(1,2,'Lily');
INSERT INTO T2 VALUES(2,2,'Tulip');
INSERT INTO T2 VALUES(2,1,'Jasmine');
INSERT INTO T2 VALUES(1,2,'Daisy');

Now, as the schema is prepared, let’s run the SQL Query:

SELECT * FROM T1 
LEFT JOIN T2 On T1.C1 = T2.C1
ORDER BY T1.C1;

The Output is not at all what I expected!

Output:

C1	C2	C3	C1	C2	C3
1	1	Apple	1	2	Daisy
1	2	Grapes	1	2	Lily
1	2	Grapes	1	2	Daisy
1	2	Cherry	1	2	Lily
1	2	Cherry	1	2	Daisy
1	1	Apple	1	1	Rose
1	2	Grapes	1	1	Rose
1	2	Cherry	1	1	Rose
1	1	Apple	1	2	Lily
2	2	Mango	2	2	Tulip
2	1	Banana	2	2	Tulip
2	2	Mango	2	1	Jasmine
2	1	Banana	2	1	Jasmine

Now, If we take a closer look, it actually makes sense.

T1.C1 = 1 for 3 records.

T2.C1 = 1 for 3 records

So, that makes 3 * 3 = 9 combinations.

T1.C1 = 2 for 2 records

T2.C1 = 2 for 2 records

That makes 2 * 2 = 4 records

So final resultset has 9 + 4 = 13 records!

Now, does that make sense? It’s tricky, Right?

Finally, if you are practicing SQL, then you can take a look at Solve SQL: SQL Exercises and Solutions in MySQL

Thank You for reading!

Happy Practicing SQL!