SQL Joins - inner join, left join, right join, full outer join, cross join

 drop table if exists table1;

create table table1(id VARCHAR(30));


insert into table1 (id) values (1), (2), (3), (4), (5), (null)

--insert into table1 values ('hello')

--error in joins if in on condition we use this table with other interger value table

-- not error if we use only table 2 interger value with integer. table.id = 2; 

--like tihs select * from table2 left join table1 on table2.id = 2;


select * from table1;


drop table if exists table2;

create table table2 (id integer);


insert into table2 (id) values (2), (2), (6), (4);

--insert into table2 (id) values ("hello");


select * from table2;


select * from table1;

select * from table2;

--inner join


select * from table1 inner join table2 on table1.id = table2.id;


select * from table1,table2 where table1.id = table2.id;


select * from table1 cross join table2 where table1.id = table2.id;

-- error if we use "on" in crossjoin. use where clause with crossjoin.

--iner join on = cross join where = same result


select * from table1 cross join table2; --rows 6*4 = 24. 


select * from table1 cross join table2 where table2.id =2 ; --rows 7*4 = 28 (varchar , interger. in varchar add 'hello' string row.) 

--no error but if we use where condition like table2.id = table1.id then there will be an error.


-- right join with out 'hello' add


select * from table1 right join table2 on table1.id = table2.id;


--left join without add 'hello'

select * from table1 left join table2 on table1.id = table2.id;

--7th row will be NULL NULL. in sql NULL <> NULL.


--FULL OUTER JOIN. UNIOUN OF LEFT AND RIGHT JOIN. 

SELECT * FROM table1 full outer join table2 on table1.id = table2.id;

Comments

Popular Posts

15 Cases where True Indology exposed Devdutt Pattanaik

Maharishi Sushruta : Father of Indian Medicine, Father of Plastic Surgery- A Great Rishi and Scholar

Mythologist Author Devdutt Pattanaik's Abusive Tweets