SQL-JOIN related Questions

Main_Interview Questons Joins

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
Total5

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
Total6

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.

 
CREATE TABLE TAB_A AS 
(SELECT 'a' AS COL_A FROM dual 
UNION ALL 
SELECT 'b' AS COL_A FROM dual 
UNION ALL 
SELECT 'c' AS COL_A FROM dual 
UNION ALL 
SELECT 'd' AS COL_A FROM dual 
UNION ALL 
SELECT 'e' AS COL_A FROM dual);
/
CREATE TABLE TAB_B AS 
(SELECT 'a' AS COL_B FROM dual 
UNION ALL 
SELECT 'b' AS COL_B FROM dual 
UNION ALL 
SELECT 'b' AS COL_B FROM dual 
UNION ALL 
SELECT 'a' AS COL_B FROM dual 
UNION ALL 
SELECT 'c' AS COL_B FROM dual);
/
--this is the first example for Inner Join query
SELECT count(*) FROM TAB_A a INNER JOIN TAB_B b 
ON a.COL_A = b.COL_B;
/
--then we added one more 'c' to table a
INSERT INTO TAB_A VALUES ('c');
--commit everything.
COMMIT;
/
-- below are the select statement with different types of JOINS.
SELECT count(*) FROM TAB_A a INNER JOIN TAB_B b ON a.COL_A = b.COL_B;
SELECT count(*) FROM TAB_A a LEFT JOIN TAB_B b ON a.COL_A = b.COL_B;
SELECT count(*) FROM TAB_A a RIGHT JOIN TAB_B b ON a.COL_A = b.COL_B;
SELECT count(*) FROM TAB_A a FULL JOIN TAB_B b ON a.COL_A = b.COL_B;

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..

Main_full outer join

SQL – Full Outer Join

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

Main_right outer join

SQL – Right Outer Join

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

Main_left outer join

SQL – 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 *