The purpose of a SQL join is to combine the data across tables.
A join is actually performed by the where clause which combines the specified rows of tables.
If
a join involves in more than two tables then oracle joins first two
tables based on the joins condition and then compares the result with
the next table and so on.SQL Join Types
Inner Join
|
This
will display all the records that have matched.
|
Left Outer
|
This
will display the all matching records and the records which are in left hand
side table those that are not in right hand side table.
|
Right Outer
|
This
will display the all matching records and the records which are in right hand
side table those that are not in left hand side table.
|
Full Outer
|
This
will display the all matching records and the non-matching records from both
tables.
|
Assume that we have the following tables.
SQL> select * from Dept;
DEPTNO
|
DNAME
|
LOC
|
10
|
Mkt
|
Hyd
|
20
|
Fin
|
Bang
|
30
|
Hr
|
Bombay
|
SQL> select * from Emp;
EMPNO
|
ENAME
|
JOB
|
MGR
|
DEPTNO
|
111
|
saketh
|
analyst
|
444
|
10
|
222
|
sudha
|
clerk
|
333
|
20
|
333
|
jagan
|
manager
|
111
|
10
|
444
|
madhu
|
engineer
|
222
|
40
|
Example:
SQL> select empno,ename,job,dname,loc from emp inner join dept using(deptno);
EMPNO ENAME JOB DNAME LOC
---------- ---------- ---------- ---------- ----------
111 saketh analyst mkt hyd
333 jagan manager mkt hyd
222 sudha clerk fin bang
LEFT OUTER JOIN
This
will display the all matching records and the records which are in left
hand side table those that are not in right hand side table.
Example:
SQL>Select empno,ename,job,dname,loc from emp e left outer join dept d on(e.deptno=d.deptno);
Or
SQL> Select empno,ename,job,dname,loc from emp e,dept d where e.deptno=d.deptno(+);
EMPNO ENAME JOB DNAME LOC
---------- ---------- ---------- ---------- ----------
111 saketh analyst mkt hyd
333 jagan manager mkt hyd
222 sudha clerk fin bang
444 madhu engineer
RIGHT OUTER JOIN
This
will display the all matching records and the records which are in
right hand side table those that are not in left hand side table.
Example:
SQL>Select empno,ename,job,dname,loc from emp e right outer join dept d on(e.deptno=d.deptno);
Or
SQL>Select empno,ename,job,dname,loc from emp e,dept d where e.deptno(+) = d.deptno;
EMPNO ENAME JOB DNAME LOC
---------- ---------- ---------- ---------- ----------
111 saketh analyst mkt hyd
333 jagan manager mkt hyd
222 sudha clerk fin bang
hr bombay
FULL OUTER JOIN
Example:
SQL>Select empno,ename,job,dname,loc from emp e full outer join dept d on(e.deptno=d.deptno);
EMPNO ENAME JOB DNAME LOC
---------- ---------- ---------- ---------- ----------
333 jagan manager mkt hyd
111 saketh analyst mkt hyd
222 sudha clerk fin bang
444 madhu engineer hr bombay
Hello,
ReplyDelete11/10!! Your blog is such a complete read. I like your approach with #topic. Clearly, you wrote it to make learning a cake walk for me.
how to find first nonspace character from end of the string
ex: 'abc def g '
I should get 4 in this case. (the first nonspace character from backwards in the above example is g and position is 4 from backwards.
Could you help me in getting this?
I am so grateful for your blog. Really looking forward to read more.
Best Regards,
Marhaba,
ReplyDeleteSeems like I won the lottery here….This is a treasure box of blogs and your folks are like leprechauns! Phenomenal read on Oracle SQL joins.
I have DMLStatements which contains some polish characters for POLAND country.
When i am executing that DML through the QL Developer i am able to see my Polishcharacters.
but when we gave the release through the SQLPLUS those characters are replacing with some special characters
like ? (Question Mark)
Data type of the column is : NCLOB
NLS_CHARACTERSET : AL32UTF8
NLS_NCHAR_CHARACTERSET : AL16UTF16
Could you please help to resolve this?
THANK YOU!! This saved my butt today, I’m immensely grateful.
Kind Regards,
Radhey