SQL SERVER INNER JOIN
Inner join used to join tables based on common keys.
INNER JOIN SYNTAX:-
select * from table 1
inner join
table 2
on condition
EMP TABLE
DEPT TABLE
>Get Employee & Department Details
Select * from EMP
inner join
Dept
on EMP.DEPTNO=DEPT.DEPTNO
Empno ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC ---- ---- ------ ----- ---- -------------------------------------------------- ----- ---- -------------------- ----- ---- ------ ----- ---- ---------- ----- ---- --------------------------------------- ----- ---- --------------------------------------- ----- ---- ------ ----- ---- ------ ----- ---- -------------------------------------------------- ----- ---- -------------------------------------------------- ----- -----
>Get DEPT Details with Employee Count.
select d.deptno,dname,loc,e."Emp Count" from dept d
inner join
(select deptno,count(*) "Emp Count" from emp group by deptno) e
on d.deptno = e.deptno;
Tags: SQLServer inner join, SQL Server JOINS, SQL Server Inner join on common keys, SQL Server Inner Join on foreign keys of child table and Primary keys of parent table.
Inner join used to join tables based on common keys.
INNER JOIN SYNTAX:-
select * from table 1
inner join
table 2
on condition
EMP TABLE
EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
7369 | SMITH | CLERK | 7902 | 12/17/1980 12:00:00 AM | 800.00 | 20 | |
7499 | ALLEN | SALESMAN | 7698 | 2/20/1981 12:00:00 AM | 1600.00 | 300.00 | 30 |
7521 | WARD | SALESMAN | 7698 | 2/22/1981 12:00:00 AM | 1250.00 | 500.00 | 30 |
7566 | JONES | MANAGER | 7839 | 4/2/1981 12:00:00 AM | 2975.00 | 20 | |
7654 | MARTIN | SALESMAN | 7698 | 9/28/1981 12:00:00 AM | 1250.00 | 1400.00 | 30 |
7698 | BLAKE | MANAGER | 7839 | 5/1/1981 12:00:00 AM | 2850.00 | 30 | |
7782 | CLARK | MANAGER | 7839 | 6/9/1981 12:00:00 AM | 2450.00 | 10 | |
7839 | KING | PRESIDENT | 11/17/1981 12:00:00 AM | 5000.00 | 10 | ||
7844 | TURNER | SALESMAN | 7698 | 9/8/1981 12:00:00 AM | 1500.00 | 0.00 | 30 |
7900 | JAMES | CLERK | 7698 | 12/3/1981 12:00:00 AM | 950.00 | 30 | |
7902 | FORD | ANALYST | 7566 | 12/3/1981 12:00:00 AM | 3000.00 | 20 | |
7934 | MILLER | CLERK | 7782 | 1/23/1982 12:00:00 AM | 1300.00 | 10 |
DEPT TABLE
Dept ID | Dept Name | Dept Loc |
10 | ACCOUNTING | NEW YORK |
20 | RESEARCH | DALLAS |
30 | SALES | CHICAGO |
40 | OPERATIONS | BOSTON |
>Get Employee & Department Details
Select * from EMP
inner join
Dept
on EMP.DEPTNO=DEPT.DEPTNO
Empno ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC ---- ---- ------ ----- ---- -------------------------------------------------- ----- ---- -------------------- ----- ---- ------ ----- ---- ---------- ----- ---- --------------------------------------- ----- ---- --------------------------------------- ----- ---- ------ ----- ---- ------ ----- ---- -------------------------------------------------- ----- ---- -------------------------------------------------- ----- -----
7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | 30 | SALES | CHICAGO |
7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | 30 | SALES | CHICAGO |
7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | 300.00 | 20 | 20 | RESEARCH | DALLAS |
7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | 30 | SALES | CHICAGO |
7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | 30 | SALES | CHICAGO |
7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | 100.00 | 10 | 10 | ACCOUNTING | NEW YORK |
7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | 30 | SALES | CHICAGO |
7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | 30 | SALES | CHICAGO |
7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | 20 | RESEARCH | DALLAS |
7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | 10 | ACCOUNTING | NEW YORK |
>Get DEPT Details with Employee Count.
select d.deptno,dname,loc,e."Emp Count" from dept d
inner join
(select deptno,count(*) "Emp Count" from emp group by deptno) e
on d.deptno = e.deptno;
10 | ACCOUNTING | NEW YORK | 3 |
20 | RESEARCH | DALLAS | 3 |
30 | SALES | CHICAGO | 6 |
Tags: SQLServer inner join, SQL Server JOINS, SQL Server Inner join on common keys, SQL Server Inner Join on foreign keys of child table and Primary keys of parent table.
No comments:
Post a Comment