Joins:
Join is a query that combines the rows from two or more queries.
Types of joins:
1. Cross Join
2. Inner join or equi join or simple join
3. Outer join
4. Self join
1. Cross join:
A join which is based equality condition is called inner join or equijoin
Join is a query that combines the rows from two or more queries.
Types of joins:
1. Cross Join
2. Inner join or equi join or simple join
3. Outer join
4. Self join
1. Cross join:
- In cross join every row in the first table joins with the every row in the second table.
- The output of cross join is called Cartesian product.
Ex: select ename,dname
from emp, dept
or
select ename,dname
from emp cross join dept
2. Equi join or Inner join: A join which is based equality condition is called inner join or equijoin
Syntax:
select table1.column1, table2.column2.... from table1, table2
where table1.column=table2.column
or
select table1.column1, table2.column2.... from table1 inner join table2
on table1.column=table2.column
Q. Write a sql query to display ename,job, deptno,dname and loc for all employees in emp table.
A. select ename, job, emp.deptno, dept.deptno, dname, loc
from emp, dept
where emp.deptno=dept.deptno
or
select ename, job, emp.deptno, dept.deptno, dname, loc
from emp inner join dept on emp.deptno=dept.deptno
| ENAME | JOB | DEPTNO | DEPTNO | DNAME | LOC |
| MARTIN | SALESMAN | 30 | 30 | SALES | CHICAGO |
| FORD | ANALYST | 20 | 20 | RESEARCH | DALLAS |
| SMITH | CLERK | 20 | 20 | RESEARCH | DALLAS |
| ALLEN | SALESMAN | 30 | 30 | SALES | CHICAGO |
| WARD | SALESMAN | 30 | 30 | SALES | CHICAGO |
| JONES | MANAGER | 20 | 20 | RESEARCH | DALLAS |
| BLAKE | MANAGER | 30 | 30 | SALES | CHICAGO |
| CLARK | MANAGER | 10 | 10 | ACCOUNTING | NEW YORK |
| SCOTT | ANALYST | 20 | 20 | RESEARCH | DALLAS |
| KING | PRESIDENT | 10 | 10 | ACCOUNTING | NEW YORK |
| TURNER | SALESMAN | 30 | 30 | SALES | CHICAGO |
| ADAMS | CLERK | 20 | 20 | RESEARCH | DALLAS |
| JAMES | CLERK | 30 | 30 | SALES | CHICAGO |
| MILLER | CLERK | 10 | 10 | ACCOUNTING | NEW YORK |
A. select ename, dname,job
from emp, dept
where emp.deptno=dept.deptno
and loc='New York'
| ENAME | DNAME | JOB |
| CLARK | ACCOUNTING | MANAGER |
| KING | ACCOUNTING | PRESIDENT |
| MILLER | ACCOUNTING | CLERK |
Q. Write a sql query to display ename, job, sal and dname from all employees working in sales department and salary is greater than 1500.
A. select ename, job, sal, dname
from emp, dept
where emp.deptno=dept.deptno
and dname='Sales'
and sal > 1500
| ENAME | JOB | SAL | DNAME |
| ALLEN | SALESMAN | 1600 | SALES |
| BLAKE | MANAGER | 2850 | SALES |
Outer Join:
- It is an extension for the equi join.
- It retrieves all the matched records from both the tables and unmatched records form first table or second table or both the tables at a time.
Types of outer joins:
1. Left outer join
2. Right outer join
3. Full outer join
1. Left outer join:
Syntax:
select table1.column1, table2.column2 from table1, table2
where table1.column (+) = table2.column
or
select table1.column1, table2.column2 from table1
left outer join table 2 on table1.column=table2.column
It will display all the matched records from table1 and table2 and unmatched records from table1.
Ex:
select empno, ename, emp.deptno, dept.deptno, dname,loc
from emp, dept
where dept.deptno (+)= emp.deptno
Right outer join:
select table1.column1, table2.column2 from table1, table2
where table1.column = + table2.column
or
select table1.column1, table2.column2 from table1
right outer join table 2 on table1.column=table2.column
It will display all the matched records from table1 and table2 and unmatched records from table1.
Full Outer Join:
select table1.column1, table2.column2 from table1
full outer join table 2 on table1.column=table2.column
It will display all the matched and unmatched records from both the tables.
Self Join: Joining the tables itself is called self join.
Q. Write a SQL Query to display the empno, ename, mgr and mgrno
select worker.empno, worker.ename, manager.ename,manager.empno
from emp worker, emp manager
where worker.mgr = manager.empno
|
No comments:
Post a Comment