SQL SERVER INNER JOIN

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

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.