Combining Related Rows - SQL Server

Problem

You want to return rows from multiple tables by joining on a known common column or joining on columns that share common values. For example, you want to display the names of all employees in department 10 along with the location of each employee's department, but that data is stored in two separate tables. You want the result set to be the following:

 ENAME       LOC
 ----------  ----------
 CLARK       NEW YORK
 KING        NEW YORK
 MILLER      NEW YORK

Solution

Join table EMP to table DEPT on DEPTNO:

 1 select e.ename, d.loc
 2   from emp e, dept d
 3  where e.deptno = d.deptno
 4    and e.deptno = 10

Discussion

The solution is an example of a join, or more accurately an equi-join, which is a type of inner join. A join is an operation that combines rows from two tables into one. An equi-join is one in which the join condition is based on an equality condition (e.g., where one department number equals another). An inner join is the original type of join; each row returned contains data from each table.

Conceptually, the result set from a join is produced by first creating a Cartesian product (all possible combinations of rows) from the tables listed in the FROM clause, as seen below:

 
 select e.ename, d.loc,
        e.deptno as emp_deptno,
        d.deptno as dept_deptno
   from emp e, dept d
  where e.deptno = 10

 ENAME      LOC            EMP_DEPTNO DEPT_DEPTNO
 ---------- -------------  ---------- -----------
 CLARK      NEW YORK               10          10
 KING       NEW YORK               10          10
 MILLER     NEW YORK               10          10
 CLARK      DALLAS                 10          20
  
 KING       DALLAS                 10          20
 MILLER     DALLAS                 10          20
 CLARK      CHICAGO                10          30
 KING       CHICAGO                10          30
 MILLER     CHICAGO                10          30
 CLARK      BOSTON                 10          40
 KING       BOSTON                 10          40
 MILLER     BOSTON                 10          40

Every employee in table EMP (in department 10) is returned along with every department in the table DEPT. Then, the expression in the WHERE clause involving e.deptno and d.deptno (the join) restricts the result set such that the only rows returned are the ones where EMP.DEPTNO and DEPT.DEPTNO are equal:

 
 select e.ename, d.loc,
        e.deptno as emp_deptno,
        d.deptno as dept_deptno
   from emp e, dept d
  where e.deptno = d.deptno
    and e.deptno = 10

 ENAME       LOC             EMP_DEPTNO  DEPT_DEPTNO
 ----------  --------------  ----------  -----------
 CLARK       NEW YORK                10           10
 KING        NEW YORK                10           10
 MILLER      NEW YORK                10           10

An alternative solution makes use of an explicit JOIN clause (the "INNER" keyword is optional):

 select e.ename, d.loc
   from emp e inner join dept d
     on (e.deptno = d.deptno)

  where e.deptno = 10

Use the JOIN clause if you prefer to have the join logic in the FROM clause rather than the WHERE clause. Both styles are ANSI compliant and work on all the latest versions of the RDBMSs in this book.

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table