Friday, April 26, 2013

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

EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
7369SMITHCLERK790212/17/1980 12:00:00 AM800.00
20
7499ALLENSALESMAN76982/20/1981 12:00:00 AM1600.00300.0030
7521WARDSALESMAN76982/22/1981 12:00:00 AM1250.00500.0030
7566JONESMANAGER78394/2/1981 12:00:00 AM2975.00
20
7654MARTINSALESMAN76989/28/1981 12:00:00 AM1250.001400.0030
7698BLAKEMANAGER78395/1/1981 12:00:00 AM2850.00
30
7782CLARKMANAGER78396/9/1981 12:00:00 AM2450.00
10
7839KINGPRESIDENT
11/17/1981 12:00:00 AM5000.00
10
7844TURNERSALESMAN76989/8/1981 12:00:00 AM1500.000.0030
7900JAMESCLERK769812/3/1981 12:00:00 AM950.00
30
7902FORDANALYST756612/3/1981 12:00:00 AM3000.00
20
7934MILLERCLERK77821/23/1982 12:00:00 AM1300.00
10


DEPT TABLE


Dept IDDept NameDept 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