|
Joins are used to retrieve data from multiple tables.
Dept table
I want the following output:
Look at the above output, Types of Joins: Equi Joins: In Equi Joins , we always use = ( equality operator ) in join condition. Ex:
In the above query emp.deptno = dept.deptno is called as join condition. Ex2:
Try writing query by yourself. You query should look like this, Select empno, ename , job, sal, dname , loc
Ex 3:
It you look at the above requirement, it is almost similar to previous query Try writing query by yourself. Select empno, ename , job, sal, deptno, dname , loc But, the above query will give an error. As deptno column is present in both emp and dept tables. There is no ambiguity problem for columns like empno, ename, job, dname ,loc We can resolve the ambiguity by mentioning the <table_name> . <col_name> in the select clause. Ex: Select empno, ename , job, sal, emp.deptno, dname , loc The common column ie deptno can also be retrieved from dept table also. Ex: The above two queries will give the same result.
Remember: We need to mention <table_name>. < col_name> in select clause to resolve the ambiguity. Can we mention <table_name> . < col_name> for all the columns in the select clause. Why not, definitely Yes You query will look like this: Select emp.empno, emp.ename, emp.job, emp.sal, emp.deptno, dept.dname , dept.loc So, compare the following two queries Query A Query B
Both Query A and Query B will give the same result. But from performance point of view So, according to coding standards, we should mention < table_name> . < col_name> for all the columns which helps in performance. But when we mention < table_name> . <col_name> for all the columns , the length of the query will be long. To overcome the length problem, we use the table alias. Table alias helps in reducing the length of the query and at the same time, performance is maintained. Ex: e is table alias for emp table.
Not only two tables , we can join three for n table . Consider the following tables EMP Table
Dept table
Areas table
I want the following output,
Empno, Ename, deptno -- from Emp table We know, we can join emp and dept tables by using the common column deptno. Remember: The Query to get the above output. Select e.empno, e.ename, e.deptno, d,dname, d.loc, a.state
Note: Note: Non Equi-Join Consider following tables Emp Table
Salgrade Table
All the employees are categorized into grades basing on sal. What is the grade of SMITH? What is the grade of CLARK? I want the following output.
Empno, ename and sal we need to get it from EMP table. To retrieve the data from multiple tables , we need joins. When sal is falls between LOSAL and HISAL, we can get the GRADE, Ex: Select e.empno, e.ename , e.sal, s.grade Note: 3) Self join: When a table is joined to itself, it is called Self join. I want the following output
Empno, ename , sal,mgr we can straight forward get from EMP table. We need to join EMP table with EMP table. Ex: Note: Outer Joins: Outer joins are extensions of equi-join. In outer joins, we get matching and non matching data. Look at the equi-join query Output of the above query:
In the output, we are not getting dname OPERATIONS and loc BOSTON. Compare EMP and DEPT tables with respect to distinct values of the common column deptno.
As there is no value 40 in emp table, we are not getting OPERATIONS and BOSTON in the output of equi join. Outer Joins will give 14 + 1 = 15 rows in the output. As we have deficiency of data in EMP table, we use the outer join operator towards the deficiency side. Select e.empno, e.ename , e.job, e.sal, e.deptno, d.dname , d.loc Compare equi-join and outer-join queries, the only difference is output of the above OUTER JOIN query
|