Basic information about Joins in Sql server

Joins are really important for writing queries in any database language. If you take SQL Server there are few joins available which can help to construct sql statemetns. The Joins are combination of keywords Intersect, Union, Union all which we have learnt during our X standards. Join can be matched to 2 or more tables (usually), you can match single table also using join i.e., called Self join.

In Sql server we have mainly
Inner Join
Left Outer Join
Right Outer Join
Full Outer Join
Cross Join
Self Join

For best understanding create following tables and insert the data in sample database. So that you can understand very easily how the join condition results.

----------------------------
Create table Table1 (id int, name varchar(10))
Create table Table2 (id int, Name varchar(10))

insert into table1 (id, name)
values (1,'A')
insert into table1 (id, name)
values (2,'AB')
insert into table1 (id, name)
values (3,'ABC')

insert into table2 (id, name)
values (1,'A')
insert into table2 (id, name)
values (2,'AB')

----------------------------
JOINS :
Inner join : A Intersect B; it means the data results which are available in both the tables.
Eg :
SELECT a.id, a.Name,b.id, b.Name from
Table1 A inner join Table2 B on a.id = b.id

Left outer Join : A union B : Whatever the data available in A table and the matching data from B Table
Eg :
SELECT a.id, a.Name,b.id, b.Name from
Table1 A Left outer join Table2 B on a.id = b.id

Right outer Join : A union B : Whatever the data available in B table and the matching data from A Table
Eg :
SELECT a.id, a.Name,b.id, b.Name from
Table1 A Right outer join Table2 B on a.id = b.id

Full outer Join : All the data from A and B tables
Eg :
SELECT a.id, a.Name,b.id, b.Name from
Table1 A Full outer join Table2 B on a.id = b.id

Cross Join : A X B ; for each row in a multiply into all rows in B. Like wise for all the rows.
SELECT a.id, a.Name,b.id, b.Name from
Table1 A cross join Table2 B

Self Join : By using self join we can match the same table for different columns.
SELECT a.id, a.Name,b.id, b.Name from
Table1 A join Table1 B on a.id = b.id

Class 7 - Views in SQL Server

VIEW IN SQL SERVER 2000
Views are nothing but saved SQL queries. Suppose you have written a query by using multiple SQL statements to view the data. But it may require that watching the data from these tables whenever required. For this you don’t need to write all these queries every time, instead you can save it as a view. View doesn’t contain any data, it is just a query.

CREATE A VIEW
This example creates a view with a simple SELECT statement. A simple view is helpful when a combination of columns is queried frequently.
CREATE VIEW titles_view
AS
SELECT title, type, price, pubdate
FROM titles
GO

JOINS :
Views can be written by using multiple tables with help of joins. There are different joins available in SQL Server. As per the requirement we can choose join to use in the queries for view.

INNER
LEFT OUTER
RIGHT OUTER
FULL
CROSS
SELF
CARTISAN

Eg : Create view ABCD_View
AS
Select a.Col1, a.col2, b.col1, b.col2
from Table_A inner join Table_B
on a.Col1 = b.Col2


Some Arguments about Views in Sql Server 2000 : •
You can create views only in the current database. However, the tables and views referenced by the new view can exist in other databases or even other servers if the view is defined using distributed queries.
• A view can reference a maximum of 1,024 columns.
• A View can be used as a security mechanism like we can given permissions for the users also.
• View names must follow the rules for identifiers and must be unique for each user. Additionally, the name must not be the same as any tables owned by that user.
• You can build views on other views and on procedures that reference views. Microsoft® SQL Server™ 2000 allows views to be nested up to 32 levels.
• You cannot associate rules or DEFAULT definitions with views.
• You cannot associate AFTER triggers with views, only INSTEAD OF triggers.
• The query defining the view cannot include the ORDER BY, COMPUTE, or COMPUTE BY clauses or the INTO keyword.
• You cannot define full-text index definitions on views.
• You cannot create temporary views, and you cannot create views on temporary tables.
• Views or tables participating in a view created with the SCHEMABINDING clause cannot be dropped, unless the view is dropped or changed so that it no longer has schema binding. In addition, ALTER TABLE statements on tables that participate in views having schema binding will fail if these statements affect the view definition.
• When a view is created, the name of the view is stored in the sysobjects table.
• If the new table (or view) structure changes, then the view must be dropped and recreated.

View can be createD by using following options for security and performance purposes.

WITH CHECK OPTIONForces all data modification statements executed against the view to adhere to the criteria set within select_statement. When a row is modified through a view, the WITH CHECK OPTION ensures the data remains visible through the view after the modification is committed.
Eg :
CREATE VIEW CAonly
AS
SELECT au_lname, au_fname, city, state
FROM authors
WHERE state = 'CA'
WITH CHECK OPTIONGO

WITH ENCRYPTIONIndicates that SQL Server encrypts the system table columns containing the text of the CREATE VIEW statement. Using WITH ENCRYPTION prevents the view from being published as part of SQL Server replication.
Eg :
CREATE VIEW CAonly
AS
SELECT au_lname, au_fname, city, state
FROM authors
WHERE state = 'CA'
WITH ENCRYPTION
GO

SCHEMABINDINGBinds the view to the schema. When SCHEMABINDING is specified, the select_statement must include the two-part names (owner.object) of tables, views, or user-defined functions referenced.
Views or tables participating in a view created with the schema binding clause cannot be dropped unless that view is dropped or changed so that it no longer has schema binding. Otherwise, SQL Server raises an error. In addition, ALTER TABLE statements on tables that participate in views having schema binding will fail if these statements affect the view definition.

Use built-in functions within a view : This example shows a view definition that includes a built-in function. When you use functions, the derived column must include a column name in the CREATE VIEW statement.
CREATE VIEW categories (category, average_price)
AS
SELECT type, AVG(price)
FROM titles
GROUP BY type
GO