SQL Fundamental Part II (Join's in database tables)

sql-repository/joins : joins tables creation code is here

1. create table country
(
con_code NUMBER(3),
con_name varchar2(25),
continent varchar(25)
);

2.  INSERT into country values('1','India','Asia');
INSERT into country values('2','Australia','Australia');
INSERT into country values('3','America','USA');
INSERT into country values('4','Jamaika','South Africa');

3.
create table states
(
s_code NUMBER(3),
con_code NUMBER(3),
s_name varchar2(25),
details varchar2(2500)
);

Alter table states ALTER Column details varchar(2500) [NOT] NULL
ALTER TABLE states ALTER COLUMN details VARCHAR (5000) NOT NULL;


6 Equi Join

It shows non matching rows of both the tables .

select
con_name, s_name
from
country, states
where
country.con_code=states.con_code;

***************
select
con_name, s_name
from
country, states
where
country.con_code!=states.con_code;

37. ******************Outer Join*******************

Left Outer joins

select
con_name, s_name
from
country, states
where
country.con_code(+)=states.con_code;

right Outer joins

select
con_name, s_name
from
country, states
where
country.con_code(+)=states.con_code;

FULL Outer joins

select
con_name, s_name
from
country
FULL OUTER JOIN  states
ON (country.con_code=states.con_code);

38. Cartesian Join  (Join without any where clause)

select
con_name, s_name
from
country, states

39. Self Join  (When parent and child both are present on the same table .It is slowet join among all)

select a.ename, b.ename from employee b, employee a
where a.job = b.dob;



**************************Multiple table Join***********************


Now we will work on scenario based questions :

1. If i want to know all the city present in india how can i get it :

select
con_name, s_name ,city_name
from
country, states ,city
where
country.con_code=states.con_code
and
states.s_code=city.s_code
and con_name ='India';