Today i will teach you on SQL joins. Many times you faced some interviews when they ask only about joins from SQL side. If you are a fresher then, they might ask about the concepts and but for experienced person, they might give you to code.

    Here is this post i am covering those topics if you have duplicated records and NULL values in your table. So i will take two small tables contains only some rows to clarify your doubts. 
If they ask how many records will come for particular JOINS and may be what are specific output will come then you will possibly tell them the correct answer. Here i am using Oracle Live to demonstrate my code.


Below is the code snippet, you can copy/paste.
	 
--Creating tables

create table table_a (id int);

create table table_b (id int);


--Inserting data to tables

insert into table_a (id) values (1);

insert into table_a (id) values (1);

insert into table_a (id) values (1);

insert into table_a (id) values (2);

insert into table_a (id) values (2);

insert into table_a (id) values (3);

insert into table_a (id) values (null);

insert into table_b (id) values (1);

insert into table_b (id) values (1);

insert into table_b (id) values (2);

insert into table_b (id) values (4);

insert into table_b (id) values (null);

insert into table_b (id) values (null);


--Querying data from the tables

select id as table_a_id from table_a;

select id as table_b_id from table_b;
Now we have successfully created two tables and  inserted the data to those tables.





Here you can see in TABLE_A we have one NULL value and in TABLE_B have two NULL values.

Now we will se different JOINS and COUNT, how they react with data.

INNER JOIN


select a.id as inner_table_a,b.id as inner_table_b from table_a a inner join table_b b on a.id=b.id;

First we have to understand how to know how much data will come if we duplicated records.
Note: NULL values will not come in Inner Join as NULL is not equal to NULL.

Simple way to calculate:
Three occurrences of 1 from table_a and two occurrences of 1 from table_b, so we have total 3*2 = 6 records
Now see, Two occurrences of 2 from table_a and one occurrences 2 from table_b, so we have total 2*1=2 records
And there is no more common records.
Total: 6 + 2 = 8 records
  

LEFT JOIN


select a.id as inner_table_a,b.id as inner_table_b from table_a a left join table_b b on a.id=b.id;

Simple way to calculate:
Three occurrences of 1 from table_a and two occurrences of 1 from table_b, so we have total 3*2 = 6 records
Now see, Two occurrences of 2 from table_a and one occurrences 2 from table_b, so we have total 2*1=2 records
Add one record count for 3 from table_a and one record count NULL from table_a
Total: 6 + 2 + 1 + 1 = 10 records


RIGHT JOIN


select a.id as inner_table_a,b.id as inner_table_b from table_a a right join table_b b on a.id=b.id;

Simple way to calculate:
Three occurrences of 1 from table_a and two occurrences of 1 from table_b, so we have total 3*2 = 6 records
Now see, Two occurrences of 2 from table_a and one occurrences 2 from table_b, so we have total 2*1=2 records
Add one record count for 4 from table_b and two record count NULL from table_b
Total: 6 + 2 + 1 + 2 = 11 records

FULL JOIN


select a.id as inner_table_a,b.id as inner_table_b from table_a a full join table_b b on a.id=b.id;
Simple way to calculate:
Three occurrences of 1 from table_a and two occurrences of 1 from table_b, so we have total 3*2 = 6 records
Now see, Two occurrences of 2 from table_a and one occurrences 2 from table_b, so we have total 2*1=2 records
Add one record count for 3 from table_a and one record count NULL from table_a, one record count for 4 from table_b and two record count NULL from table_b
Total: 6 + 2 + 1 + 1 + 1 + 2 = 13 records


COUNT

select count(*) from table_a;
select count(id) from table_a;
Here you can see while using count(*) on table_a, we are getting counts with NULL values but while we doing count on a particular column NULL's are not counted as there is one NULL value in table_a.

If you like this post, do comment and share with your friends and colleagues.