Dear Readers,
In this article, we will see the SQL Joins.
Joins
A join is a query that combines rows from two or more tables
Join Conditions
Most join queries contain WHERE clause conditions that compare two columns, each from a different table.
Such a condition is called a join condition.
Equijoins
An equijoin is a join with a join condition containing an equality operator (=).
An equijoin combines rows that have equivalent values for the specified columns.
Example : Here we are joining two tables (Emp, Dept).
To get the selected columns from two tables
|
1 |
SELECT EMPNO,JOB,SAL,COMM,DNAME,LOC FROM EMP,DEPT WHERE DEPT.DEPTNO=EMP.DEPTNO; |
To get all the information from two tables
|
1 |
SELECT * FROM EMP, DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO; |
If we want to select the columns present in both the tables we have to specify from which table we’re picking
For example Deptno column present in both the tables, So we have to specify which deptno we’re selecting
|
1 |
SELECT EMPNO, ENAME, SAL, DNAME, LOC,EMP.DEPTNO,DEPT.DEPTNO FROM EMP, DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO; |
We can write the above query using Alias names
|
1 |
SELECT E.EMPNO,E. ENAME, E.SAL, D.DNAME, D.LOC,E.DEPTNO,D.DEPTNO FROM EMP E, DEPT D WHERE E.DEPTNO=D.DEPTNO; |
In the above query, E & D are alias names for the tables EMP & DEPT
Below query gives the list of employees who are working in department SALES
|
1 |
SELECT EMPNO,ENAME,SAL,DNAME,LOC FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND DNAME='SALES'; |
More Examples :
|
1 |
SELECT EMPNO,ENAME,SAL,DNAME,LOC FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND DNAME='SALES'AND SAL>1000; |
|
1 |
SELECT EMPNO,ENAME,SAL,DNAME,LOC FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND DNAME IN ('RESEARCH','SALES')AND SAL>1000; |
Non-Equi Joins:
Non-equi joins is used to return result from two or more tables where the exact join is not possible.
To get all the information of the employees along with salary grade and high salary, low salary
|
1 |
SELECT * FROM EMP,SALGRADE WHERE EMP.SAL BETWEEN SALGRADE.LOSAL AND SALGRADE.HISAL; |
The Below query gives the employee grades based on their salaries
|
1 |
SELECT EMPNO,ENAME,SAL,GRADE FROM EMP,SALGRADE WHERE EMP.SAL BETWEEN SALGRADE.LOSAL AND SALGRADE.HISAL; |
(Or)
|
1 |
SELECT EMPNO,ENAME,SAL,GRADE FROM EMP,SALGRADE WHERE SAL BETWEEN LOSAL AND HISAL; |
Joining three tables (EMP, DEPT, SALGRADE)
|
1 |
SELECT EMPNO,ENAME,JOB,DNAME,LOC FROM EMP,DEPT,SALGRADE WHERE SAL BETWEEN LOSAL AND HISAL AND EMP.DEPTNO=DEPT.DEPTNO; |
SELF JOIN
A self-join is a join of a table to itself.
To get the list of employee names and their managers
|
1 |
SELECT E.EMPNO,E.ENAME,M.ENAME FROM EMP E,EMP M WHERE E.MGR=M.EMPNO; |
To display the MGR ename as Manager
|
1 |
SELECT E.EMPNO,E.ENAME,M.ENAME AS MANAGER FROM EMP E,EMP M WHERE E.MGR=M.EMPNO; |
To get the list of employees who are working under manager KING
|
1 |
SELECT E.EMPNO,E.ENAME,M.ENAME AS MANAGER FROM EMP E,EMP M WHERE E.MGR=M.EMPNO AND M.ENAME='KING'; |
To get the manager name of employee BLAKE
|
1 |
SELECT E.EMPNO,E.ENAME,M.ENAME AS MANAGER FROM EMP E,EMP M WHERE E.MGR=M.EMPNO AND E.ENAME='BLAKE'; |
INNER JOIN
The most frequently used and important of the joins is the INNER JOIN. They are also referred to as an EQUIJOIN.
The INNER JOIN creates a new result table by combining column values of two tables (table1 and table2) based upon the join-predicate.
The basic syntax of INNER JOIN is as follows:
|
1 2 3 4 |
Sys>>SELECT table1.column1, table2.column2... FROM table1 INNER JOIN table2 ON table1.common_filed = table2.common_field; |
Example:
|
1 |
SELECT EMP.ENAME,DEPT.DNAME,EMP.SAL FROM EMP INNER JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO; |
(Or)
|
1 |
SELECT ENAME,DNAME,SAL FROM EMP INNER JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO; |
We can also write INNER JOIN as JOIN
LEFT JOIN:
This join returns all the rows of the table on the left side of the join and matching rows for the table on the right side of join. The rows for which there is no matching row on the right side, the result-set will contain null. LEFT JOIN is also known as LEFT OUTER
The basic syntax of LEFT JOIN is as follows:
|
1 2 3 4 |
Sys>>SELECT table1.column1, table2.column2... FROM table1 LEFT JOIN table2 ON table1.common_filed = table2.common_field; |
Example:
|
1 |
SELECT EMP.ENAME,DEPT.DNAME,EMP.SAL FROM EMP LEFT OUTER JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO; |
RIGHT JOIN
RIGHT JOIN is similar to LEFT JOIN. This join returns all the rows of the table on the right side of the join and matching rows for the table on the left side of join. The rows for which there is no matching row on left side, the result-set will contain null. RIGHT JOIN is also known as RIGHT OUTER JOIN.
Syntax:
|
1 2 3 4 |
Sys>>SELECT table1.column1, table2.column2 FROM table1 RIGHT JOIN table2 ON table1.common_filed = table2.common_field; |
Example :
|
1 |
SELECT EMP.ENAME,DEPT.DNAME,EMP.SAL FROM EMP RIGHT OUTER JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO; |
FULL JOIN
FULL JOIN creates the result-set by combining result of both LEFT JOIN and RIGHT JOIN. The result-set will contain all the rows from both the tables. The rows for which there is no matching, the result-set will contain NULL values.
Syntax:
|
1 2 3 4 |
Sys>>SELECT table1.column1, table2.column2... FROM table1 FULL JOIN table2 ON table1.common_filed = table2.common_field; |
Example :
|
1 |
SELECT EMP.ENAME,DEPT.DNAME,EMP.SAL FROM EMP FULL JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO; |
Cartesian Products :
If two tables in a join query have no join condition, Oracle returns their Cartesian product.
Oracle combines each row of one table with each row of the other.
A Cartesian product always generates many rows and is rarely useful. For example, the Cartesian product of two tables, each with 100 rows, has 10,000 rows.
Always include a join condition unless you specifically need a Cartesian product.
The basic syntax of Cartesian products is as follows:
|
1 |
Sys>select * from table1, table2; |
Example :
|
1 |
SELECT * FROM EMP,SALGRADE; |
Loading...
