JOINS IN SQL SERVER

Dept Table

EMP TABLE

Joins

Retrieving data from multiple tables using single select statement is called as Join

To perform joins you can use either on ANSI syntax or NON ANSI syntax.

ANSI syntax[American National Standard Institute]

Select * /<collist> from <table1> from inner/outer/cross join <table2> on [<join conditions> [inner/outer/cross join] <table3> [<on join condition>]

NON ANSI SYNTAX

Select */ <collist> from <table1> ,<table2> <table3>  [where conditions>]

Joins are classified into three types

·       Inner join

·       Outer join

·       Cross join

Inner join: is the join that displays only the rows that satisfy the given condition

These are again classified into four types

Equi join
non equi join
self join
natural join

Equi join: that uses equal operator in the join condition

Examples:

Display Employee Name and dname?

select ename,DNAME from emp, DEPT where EMP.DEPTNO=DEPT.DEPTNO

or

select* fromemp e inner join dept d on e.DEPTNO=d.DEPTNO

Display the names of employees working for sales?

select e.ename from emp e ,dept d where e.DEPTNO=d.DEPTNO and d.DNAME='sales'

select e.ename from emp e inner join dept d on e.DEPTNO=d.DEPTNO and d.DNAME='sales'

or

select* fromemp e inner join dept d on e.DEPTNO=d.DEPTNO

Display the names of employees working for sales?

select e.ename from emp e ,dept d where e.DEPTNO=d.DEPTNO and d.DNAME='sales'

select empno,ename,sal,deptno ,dname,loc

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

Joining three tables

select e.ename ,d.dname ,x.expr from emp e inner join dept d on e.DEPTNO=d.DEPTNO inner join emp_expr x on x.empno=e.EMPNO

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

Natural join: A join is said to natural join only if it satisfies three conditions

§  Join must be equi join

§  All common cols in the table must be in the join condition

§  Only one set of common col to be displayed in the output

Display the details of EMP along with the DEPT name and location in which EMP is working

SELECT E.*, D.DNAME, D.LOC FROM EMP E INNER JOIN DEPT D ON E.DEPTNO=D.DEPTNO

           OR

SELECT E.*, D.DNAME, D.LOC FROM EMP E, DEPT D WHERE E.DEPTNO=D.DEPTNO

Non Equi join- That does not uses equal operator in the join condition.

Display the details of employees along with grade of the employee based on salary?

select empno,ename, sal,losal,hisal,grade from emp inner join SALGRADE  on sal between losal and HISAL

Display the names of employees whose grade=3

select e.ename from emp e inner join salgrade g on e.sal between g.losal and g.hisal and g.grade=3

self join

The same table must be listed twice in different alias.

A self-join is joining the table to itself.

To perform self-join the same table must be listed twice in different alias.

Self join is performed in table having self referential integrity.

Display the details of employees along with the manager name of the employee?

select  e.EMPNO,e.ename ,e.job,e.mgr ,m.ename as manager from emp e  inner join emp m on e.MGR=m.EMPNO

To get the record who is not having mgr as null then we have to use left outer join as follows

select e.empno,e.ENAME,e.job,e.mgr,m.ename as manager   from emp e left outer join emp m on e.MGR=m.EMPNO

output:

Outer Join

Is the join that can display the rows that does not satisfy the given join condition along with the rows that satisfy the given join condition.

Outer join are classified into three types

1 left outer join

2 right outer join

3 full outer join

 Left outer join

It returns all records from the left side table and matching records from right side table.

To perform left outer join with ANSI syntax use the keyword “left outer join” with Non ANSI syntax use the operator *= in the join condition.

Display the details of employee along with deptname, location in which employee is working and also display the details of employee who are working in department that is not in dept. table.?

select * from emp e left outer join dept d on e.DEPTNO=d.DEPTNO

NON ANSCI

select * from emp e ,dept d where e.DEPTNO*=d.DEPTNO

Right outer join

It returns all the records from right side table and matching records from left side.

To perform right outer join with ANSI syntax use the keyword “RIGHT OUTER JOIN” and to perform Right Outer Join with NON ANSI syntax use the operator “=*”

Display the details of employee along with deptname and location in which the employee is working and also display the details of departments in which there are no employees.

Non ansci

select * from emp e ,dept d where e.DEPTNO=*d.DEPTNO

ansi

select * from emp e right outer join dept d on e.DEPTNO=d.DEPTNO

Full outer join

This join combines left outer join and right outer join. It returns row from either table when the conditions are met and returns null value when there is no match.

To perform ansi syntax use the keyword  “full outer join” and to perform full outer join with NON ANSI syntax perform “ UNION ON LEFT OUTER JOIN AND RIGHT OUTER JOIN STATEMENT”

Example

Display the details of employees along with department name and location in which employee is working and also display details of employees who are working in a department that is not in department table and the details of employee in which there are no employee.

select * from emp e full outer join dept d on e.DEPTNO=d.DEPTNO

non ansci syntax

SELECT * FROM EMP E  , DEPT D WHERE E.DEPTNO*=D.DEPTNO

UNION

SELECT * FROM EMP E, DEPT D WHERE E.DEPTNO=*D.DEPTNO

NOTE:   NON ANSI SYNTAX OF OUTER JOIN IS NOT SUPPORTED FROM SQL SERVER 2005 ONWARDS EVEN IF IT SUPPORTED, IT WORKS WITH ONLY “EQUAL OPERATOR”

Left Excluding JOIN

This will return all of the records in the left table (table A) that do not match any records in the right table (table B). This Join is written as follows:

SELECT D.*, E.*

FROM DEPT D

LEFT JOIN EMP E ON D.DEPTNO = E.DEPTNO

WHERE E.DEPTNO IS NULL

Right Excluding JOIN

This query will return all of the records in the right table (table B) that do not match any records in the left table (table A). This Join is written as follows:

SELECT D.*,E.*

FROM EMP E

RIGHT JOIN DEPT  D ON D.DEPTNO = E.DEPTNO

WHERE D.DEPTNO IS NULL

Outer Excluding JOIN

This query will return all of the records in the left table (table A) and all of the records in the right table (table B) that do not match. 

Cross Join

Is the join that joins every row in first table with every row in second table with out a join condition.

The result of cross join is called as Cartesian product.

Examples:

Display the details of employees along with every department available in department table?

Select * from emp cross join dept order by empno

Non ansci syntax

select * from emp,dept

Note:

Rule for NON ANSI join

A rule to follow while using Non Ansi syntax of join is no of join conditions in the where clause must be min one less than no of table you are joining and within these conditions all tables  which we are joining must be included otherwise it will perform cross join.

Examples of Joins

Display the details of employees along with the department name and location in which employee is working, manager name of employee, grade of employee based on salary?

select e.EMPNO,e.ENAME,m.ENAME as manager,e.SAL,losal,hisal,grade,e.DEPTNO,dname  from emp e left outer join emp m

on e.MGR=m.EMPNO inner join salgrade on e.sal between losal and HISAL  inner join dept d on e.DEPTNO=d.DEPTNO

non ansi

select e.EMPNO,e.ENAME,e.MGR,m.ENAME as manager,e.SAL,losal,hisal, GRADE,e.DEPTNO,dname,LOC        

 from emp e, emp m,salgrade,dept d where e.MGR=m.EMPNO and e.SAL between losal and HISAL

 and e.DEPTNO=d.DEPTNO

VIEWS IN SQL SERVER

Dept Table

Views:

A view is like a window through which user can access a table.

A view has the following three main purposes

1 Restrict the user from performing insert, update and delete only on specific rows of the table

2 Restrict the user from accessing only specific rows of the table.

3 Simplify the complex queries like set operators joins and sub queries when we have to repeatedly execute that statement.

Creating a View:

To create a view use the create view command that has the following syntax:

Create view <view name> ((columnist)) [with encryption][,][schema binding]] as <select statement>

[with check option]

Create a view to restrict the user to access only the details of employee working in deptno=20

create view emp20 as select * from emp where deptno=20

 Important:

Once the view is created it can be used like a table and you can perform select, insert, update, and delete on the view same as on the table.

But a view does not contain any data physically and whatever you select any data from a view, it will execute the select statement specify while creating a view gets data from the table and displays it to you.

When you perform insert, update, delete on a view than it will perform that insert, update & delete on the table on which the view was created. Hence the view is called as a logical object and not the physical object.

Views will not store data and will not use memory. Views contains only the structure or columns by using those views will display values from the tables. View will not contain data of its own the data which it displays will be from the table.

 The following examples retrieves data from the view emp20

select * from emp20

Example:

The following example inserts a row into the view emp20

insert emp20 values(1001,'A','clerk',7902,'10/21/1980',2000,NULL,30)

Check option: The above statement will successfully insert a row even if it is not satisfying the condition specified in select statement of the view.

Emp20 that is dept no 20 because the condition is specified in select statement of the view will be checked only during the select and not during insert, update, delete.

To check the condition specify in the select statement of the view during insert, update and delete also you must specify the “check option” while creating the view.

The following example alter the view emp20 to include check option

alter view emp20 as

select * from emp where DEPTNO=20

with check option

Note:

Using grant and revoke we can restrict user operations.

But by using view we can restrict data (particular columns)

Now we will execute the same insert query again and check what happens

insert emp20 values(1005,'A','clerk',7902,'10/21/1980',2000,NULL,30)

Error: The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint.

The statement has been terminated.

Types of Views:

Simple view

Complex view

Simple view:

-        These Views as based upon a single table, which access the data from the single table.

-        They contain a Sub Query which retrieves the data from one base table.

Syntax

Create view <viewname>

(<with option>)

As

Select statement

[where condition]

Example:

createview v1 as

selectempno,ename, sal from emp

When the above view is created select statement is stored in dB, that why view is also called as stored query.

CREATE VIEW SIMPLE_VIEW

AS SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP

Once the view is created we can access the data from it as if it was a table as following:

    SELECT * FROM SIMPLE_VIEW

SELECT EMPNO, ENAME, SAL, SAL*12 AS [ANNUAL SAL], DEPTNO FROM SIMPLE_VIEW

-We can also perform DML operations on the Simple Views which will effect on the base table.

INSERTINTO SIMPLE_VIEWVALUES(1234, 'SANTOSH', 4300, 20)

DELETEFROM SIMPLE_VIEWWHERE DEPTNO=20

UPDATEEMP SET SAL=5600 WHERE EMPNO=1001

All the columns that are referenced in the view can be modified through the view.

We cannot perform insert operations on the view if he view does not contain all the not null columns of the base table.

Example

createview v4

as

selectempno, sal,deptno from emp

wheredeptno=10

withcheck option

insertinto v4 values(100,900,10)

insert into v4 values(100,900,30)

 

Invalid because the view is created with check option

Complex Views:

-        If the View is based on multiple tables it is a complex view

-        If it is based on a single table with any of the following:

o  Group By Clause

o  Having Clause

o  Group Functions

o  Distinct Function

o  Function Calls

CREATEVIEW EMP_GRADE

    AS

    SELECT E.EMPNO, E.ENAME, E.SAL, S.GRADE, S.LOSAL, S.HISAL

    FROM EMPE INNER JOIN SALGRADE S

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

CREATEVIEW EMP_DEPT

AS

SELECTE.EMPNO, E.ENAME, E.SAL, D.DEPTNO, D.DNAME, D.LOC

FROMEMP E INNER JOIN DEPT D

ONE.DEPTNO=D.DEPTNO

       CREATE VIEW EMP_GRADE1

    AS

    SELECT E.EMPNO, E.ENAME, E.SAL, S.GRADE, S.LOSAL, S.HISAL

    FROM EMPE INNER JOIN SALGRADE S

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

CREATEVIEW EMP_DESIGNATIONS

AS

SELECTJOB FROM EMP WHERE DEPTNO=10

UNION

SELECTJOB FROM EMP WHERE DEPTNO=20

UNION

SELECTJOB FROM EMP WHERE DEPTNO=30

CREATEVIEW EMP_MAX_SAL

AS

SELECTDEPTNO, MAX(SAL) AS [HIGH SAL] FROM EMP GROUP BY DEPTNO

Create a view that provides access to details of employees along with manager Name of the employee, grade of the employee based on salary ,and deptname and location in which employee is working.

createview empdetails

as

selecte.EMPNO,e.ENAME,e.JOB,e.MGR,m.ENAME as manager,e.SAL ,losal,hisal,grade,e.DEPTNO,d.DNAME,d.LOC

 from emp e inner join emp m on e.MGR=m.EMPNO inner join salgrade on e.SAL between losal and hisal inner join dept d on e.DEPTNO =d.DEPTNO

Note:

When a view is complex view then insert, update, delete are not allowed on that view but when the view is created on multiple tables, than if you write insert, update statement that effect only one table then they are not allowed.

Query fired on view takes more time than query fired on base table. Views degrades performance.

We can also classify views as Updateable Views and Non Updateable Views:

-        A View, which allows manipulations on it, is known as Updateable View.

-        A View, which will not allow manipulations on it, is known as Non Updateable View.

If we want to perform manipulations on the Complex Views we have the following restrictions:

Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table.

• The columns being modified cannot be affected by GROUP BY, HAVING, or DISTINCT clauses.

With Check Option:

-        Forces all data modification statements executed against the view to follow the criteria set within select statement.

-        When a row is modified through a view, the WITH CHECK OPTION makes sure the data remains visible through the view after the modification is committed.

-         

-        CREATE VIEW SALES_EMP

-        AS

-        SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP WHERE DEPTNO=20

-         

-        INSERT INTO SALES_EMP VALUES(1050, ‘RAJU’, 3500, 30)

-         

-        -The above insert statement executes even if it does not satisfy the condition in the View, if this has to be restricted the view has to be created by using With Check Option clause.

ALTER VIEW SALES_EMP

AS

SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP WHERE DEPTNO=20

WITH CHECK OPTION

View Attributes:

Encryption:

If the view is created with encryption, then view definition will be hidden in information schema views table.

Schemabinding:

-        When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition.

-        We need to specify the column names individual in the select statement, cannot use “*” in the select statement.

-        All referenced objects must be in the same database.

-        Tables that participate in a view created with the SCHEMABINDING clause cannot be dropped unless that view is dropped or changed so that it no longer has schema binding.

-        CREATE VIEW EMP_BIND

-        WITH SCHEMABINDING

-        AS

-        SELECT EMNO, ENAME, JOB, MGR FROM DBO.EMP

-         

After the view is created EMP table cannot be dropped.

When a view is created with schema binding option than it is not possible to alter the columns that are selected into the view within the table until the views exists.

While creating a view with schema binding option, within the select statement of the view you must specify the table name in the format “schemaname.tablename” where schema name is same as username.

The following examples creates a view that provides access to empno, ename, job, Sal and comm by specifying schema binding.

Create view empview

With schema binding as

Select empno, ename, job, Sal, comm from dbo.emp

Now, we will try to alter.

Alter table EMP alter column sal small money

Error

Getting List of Views:

To get list of view available in the dB, you can use any of the following two options

It gets the definition of the view, table name

Select* from INFORMATION_SCHEMA.VIEWS

Select* from sys.views

(View date, created data, modified data)

To get list of the tables on which view was created use the following select statement.

Getting definition of a view:

To get the definition of a view, use the stored procedure sp_helptext that has the following syntax:

sp_helptext ‘view name’

Example

sp_helptext'v4'

Note:

When you don’t want to display the definition of your view to anyone then specify encryption option while creating the view.

To delete the view

To delete the view use the drop view command that has the following syntax

Drop view <view name>

The following examples deletes the view v4

Dropview v4