“no of records in output” sql interview questions when joining tables have duplicate and null values
In general, you have been asked questions about “no of records in output” given a sql query in interviews. In this article, we will analyz given tables, joined queries and no of records according to sql join types, duplicate values and null values.
Firstly, let’s take the basic scenario to complex step by step.
We have two tables. Table1 and Table2. Table1 has (bigint) id1 column (not primary key). Table2 has (bigint) id2 column (not primary-key).
Let’s add 2 records with value 1 to the table1.
insert into table1 values(1);
insert into table1 values(1);
Let’s add 3 records with value 1 to the table2.
insert into table2 values(1);
insert into table2 values(1);
insert into table2 values(1);
Step1- no of records in output when execute queries
--inner-join
select * from table1
inner join table2 on
table1.id1 = table2.id2
--left-join
select * from table1
left join table2 on
table1.id1 = table2.id2
--right-join
select * from table1
right join table2 on
table1.id1 = table2.id2
--full-outer-join
select * from table1
full outer join table2 on
table1.id1 = table2.id2- inner-join query.
- Table1 has 2 records values 1.
- Table2 has 3 records values 1.
- First 1 value in table1 match first 1 value in table2. Table2 has 3 records value 1. So matching 3.
- Second 1 value in table1 match first 1 value in table2. Table2 has 3 records value 1. So matching 3.
Total : 6 records
2. left join query.
So matching values from table1 and table2 and also non-matching values from table1
- you look at the example of inner join query above, you see table1 and table2 match 6 records.
- In left join query, result set has non-matching values of table1. But in this scenario, all values of table1 match with all values of table2. There is any non-matching values. So total records are 6 again.
Total : 6 records
3. right join query
So matching values from table1 and table2 and also non-matching values from table2.
- you look at the example of inner join query above, you see table1 and table2 match 6 values .
- In right join query, result set has non-matching values of table2. But in this scenario, all values of table2 match with all values of table1. There is any non-matching values. So total records are 6 again.
Total : 6 records
4. full outer join
In full outher join, result set has matching values and also non-matching values of both of tables
- In this scenario, all values of table1 match with all values of table2, all values of tables are matching. So total records are 6 again.
Total : 6 records
Now, let’s change our values/scenario
We add new values into to table1 and table2
-- add 2 into table1
insert into table1 values(2);
-- add 3 into table2
insert into table2 values(3); Table1 has a non-matching value (2)
Table2 has a non-matching value (3)
Step2- no of records in output when execute queries
--inner-join
select * from table1
inner join table2 on
table1.id1 = table2.id2
--left-join
select * from table1
left join table2 on
table1.id1 = table2.id2
--right-join
select * from table1
right join table2 on
table1.id1 = table2.id2
--full-outer-join
select * from table1
full outer join table2 on
table1.id1 = table2.id2- inner join query
- Result set has matching values of table1 and table2.
- You can see above, total matching records are 6.
Total : 6 records
2.left join query
- Result set has matching values of table1 and table2 and also non-matching values of table1
- Total count of matching values are 6 (see inner join query)
- Table1 has a non-matching value (2). +1
- Total records is 6+1 = 7
Total : 7 records
3.right join query
- Result set has matching values of table1 and table2 and also non-matching values of table2
- Total count of matching values are 6 (see inner join query)
- Table2 has a non-matching value (3). +1
- Total records is 6+1 = 7
Total : 7 records
4.full outer join query
- In full outher join, result set has matching values and also non-matching values of both of tables
- Total count of matching values are 6 (see inner join query)
- Table1 has a non-matching value (2). +1
- Table2 has a non-matching value (3). +1
- Total records is 6+1+1 = 8
Total : 8 records
Now, let’s change our values/scenario
We add new values into to table1 and table2
-- add 2 into table1
insert into table1 values(2);
-- add 2 into table2
insert into table2 values(2);
Table1:
Table2:
Step3- no of records in output when execute queries
--inner-join
select * from table1
inner join table2 on
table1.id1 = table2.id2
--left-join
select * from table1
left join table2 on
table1.id1 = table2.id2
--right-join
select * from table1
right join table2 on
table1.id1 = table2.id2
--full-outer-join
select * from table1
full outer join table2 on
table1.id1 = table2.id2- inner join query
- Result set has matching values of table1 and table2.
- Before, for values 1, total count of matching records are 6
- Now, for value 2, table1 has 2 records with value 2. Table2 has 1 record with value 2.
- First 2 value on table1 matches 2 value on table2 (+1)
- Second 2 value on table1 matches 2 value on table2 (+1)
- Total count is 6+1+1 = 8
Total : 8 records
2.left join query
- Result set has matching values of table1 and table2 and also non-matching values of table1
- Total count of matching values are 8 (see inner join query)
- Table1 has any non-matching values.
- Again total count is 8
Total : 8 records
3. right join query
- Result set has matching values of table1 and table2 and also non-matching values of table2
- Total count of matching values are 8 (see inner join query)
- Table2 has non-matching value 3. (+1)
- Total count is 8+1 = 9
Total : 9 records
4.full outer join
- In full outher join, result set has matching values and also non-matching values of both of tables
- Total count of matching values are 8 (see inner join query)
- Table1 has any non-matching values.
- Table2 has non-matching value 3. (+1)
- Total count is 8+1 = 9
Total : 9 records
Now, let’s change our values/scenario
We add new values into to table1 and table2
-- add 4 into table1
insert into table1 values(4);
-- add 2 into table2
insert into table2 values(2);Table1:
Table2:
Step4- no of records in output when execute queries
--inner-join
select * from table1
inner join table2 on
table1.id1 = table2.id2
--left-join
select * from table1
left join table2 on
table1.id1 = table2.id2
--right-join
select * from table1
right join table2 on
table1.id1 = table2.id2
--full-outer-join
select * from table1
full outer join table2 on
table1.id1 = table2.id2- inner join query
- Result set has matching values of table1 and table2.
- Before, for values 1, total count of matching records are 6
- Now, for value 2, table1 has 2 records with value 2. Table2 has 2 record with value 2. total count of matching records are 4
- Total count is 6+4 = 10
Total : 10 records
2. left join query
- Result set has matching values of table1 and table2 and also non-matching values of table1
- Total count of matching values are 10 (see inner join query)
- Table1 has non-matching value 4. (+1)
- Total count is 10+1 = 11
Total : 11 records
3. right join query
- Result set has matching values of table1 and table2 and also non-matching values of table2
- Total count of matching values are 10 (see inner join query)
- Table2 has non-matching value 3. (+1)
- Total count is 10+1 = 11
Total : 11 records
4. full outer join
- In full outher join, result set has matching values and also non-matching values of both of tables
- Total count of matching values are 10 (see inner join query)
- Table1 has non-matching value 4. (+1)
- Table2 has non-matching value 3. (+1)
- Total count is 10+1+1 = 12
Total : 12 records
Now, let’s change our values/scenario
We add NULL values into to table1 and table2
-- add null to table1
insert into table1 values(null);
-- add null to table2
insert into table2 values(null);Table1:
Table2:
Step5- no of records in output when execute queries
--inner-join
select * from table1
inner join table2 on
table1.id1 = table2.id2
--left-join
select * from table1
left join table2 on
table1.id1 = table2.id2
--right-join
select * from table1
right join table2 on
table1.id1 = table2.id2
--full-outer-join
select * from table1
full outer join table2 on
table1.id1 = table2.id2NOTE : Before execute query, you must remember:
“null value not equal null value”
“null is unknow”
“So null value is not joined”
1.inner join query
- Result set has matching values of table1 and table2.
- Before, for values 1, total count of matching records are 6
- Now, for value 2, table1 has 2 records with value 2. Table2 has 2 record with value 2. total count of matching records are 4
- null value on table1 doesn’t match with null value on table2
- Total count is 6+4 = 10
Total : 10 records
2. left join query
- Result set has matching values of table1 and table2 and also non-matching values of table1
- Total count of matching values are 10 (see inner join query)
- Table1 has non-matching value 4. (+1)
- Table1 has null value. It doesn’t match with table2 (+1)
- Total count is 10+1+1 = 12
Total : 12 records
Note : In last row, null-null record. This is not mathing record.
3.right join query
- Result set has matching values of table1 and table2 and also non-matching values of table2
- Total count of matching values are 10 (see inner join query)
- Table2 has non-matching value 3. (+1)
- Table2 has null value. It doesn’t match with table1 (+1)
- Total count is 10+1+1 = 12
Total : 12 records
Note : In last row, null-null record. This is not mathing record.
4. full outer join query
- In full outher join, result set has matching values and also non-matching values of both of tables
- Total count of matching values are 10 (see inner join query)
- Table1 has non-matching value 4. (+1)
- Table2 has non-matching value 3. (+1)
- Table1 has null value. It doesn’t match with table2 (+1)
- Table2 has null value. It doesn’t match with table1 (+1)
- Total count is 10+1+1+1+1 = 14
Total : 14 records
Note : In last row and row number 12 , null-null record. This is not mathing record.
I hope this article is useful for you.
Thanx for reading.
