Joins and how to use in Realtime
JOINS:
In RDBMS data can be stored in multiple tables from those multiple tables.
if we want to retrieve the required data / information then we use a technique is called
as “JOINS”.
– By using joins we are retrieving data / information from multiple tables at a time.
– In oracle database join statements can write into two formats.
1. Non-Ansi Format Joins(oracle8i version)
> Equi join
> Non-equi join
> Self join
2. Ansi Format Joins (oracle9i version)
> Inner join
> Outer joins
– left outer join
– right outer join
– full outer join
> Cross join
> Natural join
Non-Ansi joins VS Ansi joins:
=========================
Non-ansi joins :
1. these are not a portabilty statement.
than two tables based on “WHERE”
clause condition.
2. these joins are joining two / more than two tables based on “WHERE” clause condition.
3. syntax for non-ansi joins:
select * from , where ;
Ansi joins :
1. these are portabilty statements(i.e we can move a join statement from one platform to another platform without any changes).
2.These joins are joining two or more then two table with “ON” clause condition.
3.syntax for ansi joins :
select * from on ;
Equi join / Inner join:
– Retrieving data from multiple tables based on an ” = ” operator.
– when we use equi join then we maintain a common column name(optional) in both tables and
their datatypes must be same(mandatory).
– Equi join is always retrieve matching rows only.
DEMO_TABLES:
SQL> SELECT * FROM COURSE;
CID CNAME CFEE
—- ———- ———-
1 ORACLE 5000
2 JAVA 7000
3 .NET 8500
SQL> SELECT * FROM STUDENT;
STID SNAME CID
———- ———- ———-
101 SMITH 1
102 ALLEN 2
103 JONES 1
104 ADAMS
Ex: Want to display student and their corresponding course details by using equi join?
NON-ANSI:
=========
SQL> SELECT * FROM STUDENT,COURSE WHERE STUDENT.CID=COURSE.CID;
(OR)
SQL> SELECT * FROM STUDENT S ,COURSE C WHERE S.CID=C.CID;
(OR)
SQL> SELECT STID,SNAME,CNAME,CFEE FROM STUDENT S,COURSE C
WHERE S.CID=C.CID;
ANSI:
=========
SQL> SELECT * FROM STUDENT INNER JOIN COURSE ON STUDENT.CID=COURSE.CID;
(OR)
SQL> SELECT * FROM STUDENT S INNER JOIN COURSE C ON S.CID=C.CID;
(OR)
SQL> SELECT STID,SNAME,CNAME,CFEE FROM STUDENT S INNER JOIN COURSE C
ON S.CID=C.CID;
RULE FOR JOINS: A row in a table is comparing with all rows of another table.
Ex: Want to display student,course details who are joined in “oracle”?
NON-ANSI:
SQL> SELECT * FROM STUDENT S,COURSE C WHERE S.CID=C.CID AND CNAME=’ORACLE’;
ANSI:
SQL> SELECT * FROM STUDENT S INNER JOIN COURSE C ON S.CID=C.CID AND CNAME=’ORACLE’;
(OR)
SQL> SELECT * FROM STUDENT S,COURSE C ON S.CID=C.CID WHERE CNAME=’ORACLE’;
EX: Want to display employees and their department details who are working in the
location is “CHICAGO”?
NON-ANSI:
SQL> SELECT EMPNO,ENAME,DNAME,LOC FROM EMP E,DEPT D
2 WHERE E.DEPTNO=D.DEPTNO AND LOC=’CHICAGO’;
ANSI:
SQL> SELECT EMPNO,ENAME,DNAME,LOC FROM EMP E INNER JOIN DEPT D
2 ON E.DEPTNO=D.DEPTNO AND LOC=’CHICAGO’;
EX: Want to display sum of salaries of each department name wise from emp,dept tables?
NON-ANSI
SQL> SELECT DNAME,SUM(SAL) SUM_OF_SALARY FROM EMP E,DEPT D
WHERE E.DEPTNO=D.DEPTNO
GROUP BY DNAME;
ANSI
SQL> SELECT DNAME,SUM(SAL) SUM_OF_SALARY FROM EMP E INNER JOIN DEPT D
ON E.DEPTNO=D.DEPTNO
GROUP BY DNAME;
EX: Want to display sum of salaries of department names from emp,dept tables
if the sum of salary of department name is more than 10000?
NON-ANSI
SQL> SELECT DNAME,SUM(SAL) SUM_OF_SALARY FROM EMP E,DEPT D
WHERE E.DEPTNO=D.DEPTNO
GROUP BY DNAME
HAVING SUM(SAL)>10000;
ANSI
SQL> SELECT DNAME,SUM(SAL) SUM_OF_SALARY FROM EMP E INNER JOIN DEPT D
ON E.DEPTNO=D.DEPTNO
GROUP BY DNAME
HAVING SUM(SAL)>10000;
EX: Want to display no.of employees working in each department from emp,dept tables
if the no.of employees of department is less than 5?
NON-ANSI
SQL> SELECT DNAME,COUNT(*) FROM EMP E,DEPT D
2 WHERE E.DEPTNO=D.DEPTNO
3 GROUP BY DNAME
4 HAVING COUNT(*)<5;
ANSI
SQL> SELECT DNAME,COUNT(*) FROM EMP E INNER JOIN DEPT D
2 ON E.DEPTNO=D.DEPTNO
3 GROUP BY DNAME
4 HAVING COUNT(*)<5;
Implementing ROLLUP,CUBE clauses with JOINS:
Ex:
NON-ANSI:
SQL> SELECT DNAME,COUNT(*) FROM EMP E,DEPT D
2 WHERE E.DEPTNO=D.DEPTNO
3 GROUP BY ROLLUP(DNAME);
ANSI:
SQL> SELECT DNAME,COUNT(*) FROM EMP E INNER JOIN DEPT D
2 ON E.DEPTNO=D.DEPTNO
3 GROUP BY ROLLUP(DNAME);
EX:
NON-ANSI
SQL> SELECT D.DEPTNO,DNAME,COUNT(*) FROM EMP E,DEPT D
2 WHERE E.DEPTNO=D.DEPTNO
3 GROUP BY CUBE(D.DEPTNO,DNAME)
4 ORDER BY DEPTNO,DNAME;
ANSI:
SQL> SELECT D.DEPTNO,DNAME,COUNT(*) FROM EMP E INNER JOIN DEPT D
2 ON E.DEPTNO=D.DEPTNO
3 GROUP BY CUBE(D.DEPTNO,DNAME)
4 ORDER BY DEPTNO,DNAME;
OUTER JOINS:
By using equi join we will retrieve matching rows only.if we want to retrieve matching and unmatching rows from both tables then use “outer joins” techniques.
i) LEFT OUTER JOIN:
Learn how to retrieve all rows from one table and matching rows from another with Left Outer Join in SQL, a powerful technique for combining data from multiple tables. Master the syntax and examples of Left Join to improve your data analysis and querying skills.
– retrieving matching rows from both tables and un matching rows from the left side table only.
ANSI:
SQL> SELECT * FROM STUDENT S LEFT OUTER JOIN COURSE C ON S.CID=C.CID;
NOTE:
=====
– If we want to write outer joins statements in non-ansi format then we should use a special operator is called as “join operator” i.e (+).
NON-ANSI:
SQL> SELECT * FROM STUDENT S,COURSE C WHERE S.CID=C.CID(+);
ii) RIGHT OUTER JOIN:
==============
Get all the rows from one table and matching rows from another with Right Outer Join in SQL, a essential technique for data integration and analysis. Understand the syntax and examples of Right Join to improve your database querying and data modeling skills
– retrieving matching rows from both tables and unmatching rows from the right
side table only.
ANSI:
SQL> SELECT * FROM STUDENT S RIGHT OUTER JOIN COURSE C ON S.CID=C.CID;
NON-ANSI:
SQL> SELECT * FROM STUDENT S,COURSE C WHERE S.CID(+)=C.CID;
iii) FULL OUTER JOIN:
– it is a combination of left outer and right outer joins.
– retrieving matching & unmatching rows fron both tables at a time.
ANSI:
SQL> SELECT * FROM STUDENT S FULL OUTER JOIN COURSE C ON S.CID=C.CID;
NON-ANSI:
SQL> SELECT * FROM STUDENT S,COURSE C WHERE S.CID=C.CID(+)
UNION
SELECT * FROM STUDENT S,COURSE C WHERE S.CID(+)=C.CID;
NON-EQUI JOIN:
– Retrieving data from multiple tables based on any operator except an ” = “
operator.
DEMO_TABLES:
===============
SQL> SELECT * FROM TEST1;
SNO NAME
———- ———-
10 SMITH
20 ALLEN
SQL> SELECT * FROM TEST2;
SNO SAL
———- ———-
10 23000
30 28000
EX:
SQL> SELECT * FROM TEST1 T1,TEST2 T2 WHERE T1.SNO>T2.SNO;
SQL> SELECT * FROM TEST1 T1,TEST2 T2 WHERE T1.SNO<T2.SNO; SQL> SELECT * FROM TEST1 T1,TEST2 T2 WHERE T1.SNO>=T2.SNO;
SQL> SELECT * FROM TEST1 T1,TEST2 T2 WHERE T1.SNO<=T2.SNO; SQL> SELECT * FROM TEST1 T1,TEST2 T2 WHERE T1.SNO!=T2.SNO;
EX: Want to fetch employees details whose salary is between low salary and high salary?
NON-ANSI:
SQL> SELECT ENAME,SAL,LOSAL,HISAL FROM EMP,
SALGRADE WHERE SAL BETWEEN LOSAL AND HISAL;
(OR)
SQL> SELECT ENAME,SAL,LOSAL,HISAL FROM EMP,
SALGRADE WHERE(SAL>=LOSAL) AND (SAL<=HISAL); ANSI: SQL> SELECT ENAME,SAL,LOSAL,HISAL FROM EMP JOIN
SALGRADE ON SAL BETWEEN LOSAL AND HISAL;
(OR)
SQL> SELECT ENAME,SAL,LOSAL,HISAL FROM EMP JOIN
SALGRADE ON (SAL>=LOSAL) AND (SAL<=HISAL);
CROSS JOIN:
– joining two or more than two tables without any condition.
– in cross join, each row of a table will joins with each row of another table
for example a table is having “m” no.of rows and the other table is having
“n” no.of rows then the result will be “mxn” rows.
EX:
NON-ANSI:
SQL> SELECT * FROM STUDENT,COURSE;
ANSI:
SQL> SELECT * FROM STUDENT CROSS JOIN COURSE;
EX:
DEMO_TABLES:
============
SQL> SELECT * FROM ITEMS1;
SNO INAME PRICE
———- ———- ———-
1 PIZZA 180
2 BURGER 80
SQL> SELECT * FROM ITEMS2;
SNO INAME PRICE
———- ———- ———-
10 PEPSI 25
11 COCACOLA 20
ANSI:
SQL> SELECT I1.INAME,I1.PRICE,I2.INAME,I2.PRICE,
2 I1.PRICE+I2.PRICE AS TOTAL_AMOUNT FROM
3 ITEMS1 I1 CROSS JOIN ITEMS2 I2;
NON-ANSI:
SQL> SELECT I1.INAME,I1.PRICE,I2.INAME,I2.PRICE,
2 I1.PRICE+I2.PRICE AS TOTAL_AMOUNT FROM
3 ITEMS1 I1,ITEMS2 I2;
NATURAL JOIN: It is a similar to equi join for retrieving matching rows from multiple tables.
Natural join VS Equi join:
Natural join ; Learn how to combine tables automatically with Natural Joins in SQL, a simple and efficient way to join tables without specifying join conditions. Master the syntax and examples of Natural Inner Join to improve your data analysis and querying skills.
1.joining tables based on “implicit condition”.
2.By default it avoid duplicate columns from the result set.
3. common column name is mandatory.
Equi join :
An Equi Join in SQL is a type of join that combines rows from two tables where the join condition is based on an equality condition, such as table1.column = table2.column. This type of join is used to retrieve data from multiple tables where the values in a common column are equal.
1. Joining tables based on “explicit condition”.
2. By default it will not avoid duplicate columns from the result set.
3. Common column name is optional.
EX: SQL> SELECT * FROM STUDENT S NATURAL JOIN COURSE C;
SELF JOIN:
A Self Join in SQL is a type of join where a table is joined with itself, allowing you to compare rows within the same table. This is useful for querying hierarchical or recursive data, such as an organizational chart or a bill of materials.
– joining a table by itself is called as “self join”
(or)
– comparing a table data by itself is called as “self join”.
– when we use self join on a table we must create alias names on that table otherwise
we cannot perform self join technique.
– once we create alias name on a table internally system is preparing virtual table
on each alias name automatically.
– we can create any no.of alias names on a single table but each alias name should
be different.
– self join can use at two cases in database,
Case-1: comparing a single column values by itself with in the table.
Case-2: comparing two different columns values to each other with in th same table.
Examples on comparing a single column values by itself with in the table:
==========================================================================
DEMO_TABLE:
============
SQL> SELECT * FROM TEST;
ENAME LOC
———- ———-
SMITH HYD
ALLEN MUMBAI
MILLER HYD
WARD PUNE
EX: Wanq to display employees who are working in the same location where the employee
SMITH is also working?
SQL> SELECT T1.ENAME,T1.LOC FROM TEST T1,TEST T2
WHERE T1.LOC=T2.LOC AND T2.ENAME=’SMITH’;
ENAME LOC
———- ———-
SMITH HYD
MILLER HYD
EX: Want to display employees whose salary is same as the employee “FORD” salary in emp
table?
ANSI:
SQL> SELECT E1.ENAME,E1.SAL FROM EMP E1 JOIN EMP E2
2 ON E1.SAL=E2.SAL AND E2.ENAME=’FORD’;
NON-ANSI:
SQL> SELECT E1.ENAME,E1.SAL FROM EMP E1,EMP E2
WHERE E1.SAL=E2.SAL AND E2.ENAME=’FORD’;
Examples on comparing two different columns values to each other with in th same table:
=========================================================================
Ex: Want to display MANAGERS and their EMPLOYEES from emp table?
SQL> SELECT M.ENAME AS MANAGER,E.ENAME AS EMPLOYEES
2 FROM EMP E,EMP M WHERE M.EMPNO=E.MGR;
Ex: Want to display employees who are joined before their manager?
SQL> SELECT E.ENAME AS EMPLOYEES,E.HIREDATE AS E_DOJ,
2 M.ENAME AS MANAGER,M.HIREDATE AS M_DOJ FROM
3 EMP E,EMP M WHERE M.EMPNO=E.MGR AND E.HIREDATE < M.HIREDATE; Ex: Want to display employees whose salary is more than their manager salary? SQL> SELECT E.ENAME AS EMPLOYEES,E.SAL AS E_SAL,
M.ENAME AS MANAGER,M.SAL AS M_SAL FROM
EMP E,EMP M WHERE M.EMPNO=E.MGR AND E.SAL > M.SAL;
Ex: Want to display employees who are working under “BLAKE” manager?
SELECT E.ENAME AS EMPLOYEES,M.ENAME AS MANAGER FROM EMP E,EMP M
WHERE M.EMPNO=E.MGR AND M.ENAME=’BLAKE’;
Ex: Want to display the manager of BLAKE employee?
SQL> SELECT M.ENAME AS MANAGER FROM EMP E,EMP M
WHERE M.EMPNO=E.MGR AND E.ENAME=’BLAKE’;
How to join more than two tables:
=================================
NON-ANSI:
=========
SELECT * FROM ,,,,………………………
WHERE and and ;
ANSI:
=====
SELECT * FROM ON
ON
ON
……
……
ON ;
DEMO_TABLE:
============
SQL> SELECT * FROM REGISTER;
REGNO REGDATE CID
———- ——— ———-
10 23-APR-24 1
22 15-MAY-22
EQUI / INNER JOIN:
=================
NON-ANSI:
SQL> SELECT * FROM STUDENT S,COURSE C,REGISTER R
2 WHERE S.CID=C.CID AND C.CID=R.CID;
ANSI:
SQL> SELECT * FROM STUDENT S INNER JOIN COURSE C
2 ON S.CID=C.CID INNER JOIN REGISTER R
3 ON C.CID=R.CID;
If you want to learn about SQL Operators then please visit our previous article click here.
If you want to learn about SQL Primary key and Foreign key relationship then please visit.
If you want to learn about SQL Functions then please visit our previous article Click here.
For online practice please click here.
