SQL-JOIN related Questions

admin Interview questions, SQL Joins ,

Hi folks,
Hope you all are doing well. Today, we will talk about a very frequently asked question in many interviews and we will discuss about how can we identify the solution very quickly?
Interviewer asked to create two tables and insert the rows as shown below.

TAB_A
COL_A
a
b
c
d
e
TAB_B
COL_B
a
b
b
a
c

Question: What is the count of row when we apply different types of Join on these tables?
I will explain you the tricks for 4 types of Join. We do have separate blogs on each topic. You can read if you are not clear with the logic.
Inner Join
Left outer Join
Right outer Join
Full outer Join

Note:
We will consider TAB_A as LEFT table and TAB_B as RIGHT table while working with Left Outer Join and Right Outer Join.

Inner Join:
We know that when we apply inner Join on two tables, the output is the common records from both the tables.
Identify the records that exist in both the tables and prepare a table as shown below.

Common_values # in TAB_A # in TAB_B
a 1 2
b 1 2
c 1 1

Next and final step is, multiply number of values in Tab_A by number of values in Tab_B and perform addition as showing in the blow table.

Common_values # in TAB_A # in TAB_B A x B
a 1 2 2
b 1 2 2
c 1 1 1
Total 5

Let’s add one more row to TAB_A with value “c” and see the results.

TAB_A
COL_A
a
b
c
d
e
c
TAB_B
COL_B
a
b
b
a
c

Let’s, assess this table again.

Common_values # in TAB_A # in TAB_B A x B
a 1 2 2
b 1 2 2
c 2 1 2
Total 6

Remember, Inner Join is the base Join of all other types of Join. It means when we apply other Joins, number of rows will be more than or equal to number of rows returned by inner Joins.

Left Join:
Left Join returns all the rows from the Left table and only matching records from the Right table. As we already have the count of matching records i.e. 6, just add count of all other records from TAB_A (Left Table) to it.

Except matching records there are 2 additional records i.e. c and d in Tab_A. So when we apply Left Join, it will return total 8 rows.

Right Join:
Right Join is just opposite to Left Join. It returns all the rows from Right table and only matching records from Left Table. As we can see there are no additional records in the Right table (TAB_B) and all records are matching with Left table (TAB_A). So we do not need to add anything to it. It will return the same rows as Inner Join.

If we had f in TAB_B, we would add it 6 and Right Join would return 7 rows.

Full Outer Join:
Full Outer Join is combination of Inner Join, Left Join and Right Join. It returns all records from Left Table and all records from Right table. As we already have count of rows based on Inner Join, Left Join and Right Join. Apply the below formula to get the number of rows returned by Full Outer Join.

Full Outer Join =
Inner Join + (Left Join – Inner Join) + (Right Join – Inner Join)

Full Outer Join = 6+(8-6)+(6-6)=8

That’s only we need to do to overcome such questions.

Here is the script you can use to verify all your work.

If you have any questions please comment below or write me an email.

If you like this blog, do share with your friends and colleagues on your social media.
For more updates join my facebook group and do like my facebook page.

Thank you,
Kapil Kumar

Sharing is caring!

You May Also Like..

Full-Outer-Join

Hi Oracle folks, This is going to be last blog on Joins, it is about Full Outer Join. What is […]

Right-Outer-Join

Hi Oracle folks, After Left Outer Join, it is time to learn about Right Outer Join. What is Right Outer […]

Left-Outer-Join

Hi Oracle folks, In this blog, we will talk about the OUTER JOIN. What is an OUTER JOIN? Outer join […]

Leave a Reply

Your email address will not be published. Required fields are marked *