SQL SERVER – JOINS

By using joins, you can retrieve data from two or more tables based on logical relationships between the tables. Joins indicate how database should use data from one table to select the rows in another table.

A join condition defines the way two tables are related in a query by:

• Specifying the column from each table to be used for the join. A typical join condition specifies a foreign key from one table and its associated key in the other table.
• Specifying a logical operator (for example, = or <>,) to be used in comparing values from the columns.

Types of Joins:

• 1. Equi-Joins
  2. Non Equi-Joins
  3. Self Joins
  4. Cartesian Joins
  5. Outer Joins
     • Left Outer Join
     • Right Outer Join

Equi-Joins:

It returns the specified columns from both the tables, and returns only the rows for which there is an equal value in the join column.

SELECT

E.EMPNO, E.ENAME, E.SAL, E.DEPTNO,

D.DEPTNO, D.DNAME, D.LOC

FROM EMP E, DEPT D

WHERE E.DEPTNO=D.DEPTNO

-The above statement is known, as old-style join statement, which will combine the tables basing on equality condition i.e. the Deptno column in the Emp table, has to have an exact match of Deptno in the Dept table, then these 2 rows combine and get retrieved. In the new-style we call this as Inner Join where we write the statement as following:

SELECT

E.EMPNO, E.ENAME, E.SAL, E.DEPTNO,

D.DEPTNO, D.DNAME, D.LOC

FROM EMP E INNER JOIN DEPT D

ON E.DEPTNO=D.DEPTNO

-In the same way if we want to combine multiple table in old-style we write following:

SELECT

E.EMPNO, E.ENAME, E.SAL, E.DEPTNO,

D.DEPTNO, D.DNAME, D.LOC,

DD.DID, DD.COMMENTS

FROM EMP E, DEPT D, DEPTDETAILS DD

WHERE E.DEPTNO=D.DEPTNO AND D.DEPTNO=DD.DEPTNO

-The same statement in the new-style we write as following:

SELECT

E.EMPNO, E.ENAME, E.SAL, E.DEPTNO,

D.DEPTNO, D.DNAME, D.LOC,

DD.DID, DD.COMMENTS

FROM EMP E INNER JOIN DEPT D

ON E.DEPTNO=D.DEPTNO

INNER JOIN DEPTDETAILS DD

ON D.DEPTNO=DD.DEPTNO

Non Equi-Joins:

You can also join values in two columns that are not equal. The same operators and predicates used for equi-joins can be used for not-equi joins.

SELECT

E.EMPNO, E.ENAME, E.SAL,

S.SALGRADE, S.LOSAL, S.HISAL

FROM EMP E, SALGRADE S

WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL

-We can write the above statement using inner join in the new style as following:

SELECT

E.EMPNO, E.ENAME, E.SAL,

S.SALGRADE, S.LOSAL, S.HISAL

FROM EMP E INNER JOIN SALGRADE S

ON E.SAL BETWEEN S.LOSAL AND S.HISAL

Self Join:

If a table has a reflexive relationship in the database, you can join it to itself automatically which is known as self join.

SELECT

DISTINCT E.EMPNO, E.ENAME, E.SAL, E.DEPTNO

FROM EMP E, EMP M

WHERE E.EMPNO=M.MGR

-We can write the above statement using inner join in the new style as following:

SELECT

DISTINCT E.EMPNO, E.ENAME, E.SAL, E.DEPTNO

FROM EMP E INNER JOIN EMP M

ON E.EMPNO=M.MGR

Cartesian Join:

A Cartesian join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table. This is also known as cross-join. However, if a WHERE clause is added, the cross join behaves as an inner join.

SELECT

E.EMPNO, E.ENAME, E.SAL, E.DEPTNO,

D.DEPTNO, D.DNAME, D.LOC

FROM EMP E, DEPT D

-We can write the above statement in the new style as following:

SELECT

E.EMPNO, E.ENAME, E.SAL, E.DEPTNO,

D.DEPTNO, D.DNAME, D.LOC

FROM EMP E CROSS JOIN DEPT D

Outer Join:

By default, when we join multiple tables using inner join what we get is the matching data from the tables, if we want to include data rows in the result set that do not have a match in the joined table, we can us outer join.

The old-style of outer joins have been classified into 2 types as Left Outer Join and Right Outer Join.

We use Left Outer Join to get the matching information plus unmatched information from left hand side table, in the same way we use Right Outer Join to get the matching information plus unmatched information from right hand side table.

Left hand side table and right hand side tables are referred in the order we write in the from clause, first table is LHS table and second table is RHS table.

SELECT

E.EMPNO, E.ENAME, E.SAL, E.DEPTNO,

D.DEPTNO, D.DNAME, D.LOC

FROM EMP E, DEPT D

WHERE E.DEPTNO=*D.DEPTNO

-In the above case we get the matching information plus unmatched information from Dept table.

SELECT

E.EMPNO, E.ENAME, E.SAL, E.DEPTNO,

D.DEPTNO, D.DNAME, D.LOC

FROM EMP E, DEPT D

WHERE E.DEPTNO*=D.DEPTNO

-In the above case we get the matching information plus unmatched information from Emp table.

-Suppose we have unmatched information in both the sides we cannot retrieve it at the same time to over come this in the new-style of join statement they have introduced Full Outer join. So the new-style supports use the following:

• LEFT OUTER JOIN
• RIGHT OUTER JOIN
• FULL OUTER JOIN

-Use Left Outer Join to get the unmatched information from left hand side table as following:

SELECT

E.EMPNO, E.ENAME, E.SAL, E.DEPTNO,

D.DEPTNO, D.DNAME, D.LOC

FROM EMP E LEFT OUTER JOIN DEPT D

ON E.DEPTNO=D.DEPTNO

-Use Right Outer Join to get the unmatched information from right hand side table as following:

SELECT

E.EMPNO, E.ENAME, E.SAL, E.DEPTNO,

D.DEPTNO, D.DNAME, D.LOC

FROM EMP E RIGHT OUTER JOIN DEPT D

ON E.DEPTNO=D.DEPTNO

-Use Full Outer Join to get the unmatched information from both the tables as following:

SELECT

E.EMPNO, E.ENAME, E.SAL, E.DEPTNO,

D.DEPTNO, D.DNAME, D.LOC

FROM EMP E FULL OUTER JOIN DEPT D

ON E.DEPTNO=D.DEPTNO

Finally concluding in the new-style we have only 3 types of joins those are Inner Joins, Cross Joins and Outer Joins in the place of Equi-Joins, Non Equi-Joins, Self Joins, Cartesian Joins and Outer Joins which are present in the old-style.