How to deal with null values in joins?

NULLs and JOINs
Related Resources • SQL Fundamentals • Starting a Career in Databases • Certification Resources In our last article, we introduced the concept of the NULL (or “unknown”) value and described how SQL treats this value when encountered in logical operations such as AND and OR.  In this article, we take a look at how NULL values impact a more complicated operation – the JOINing of two tables. Let's begin by taking a look at two sample tables that comprise the course database for a school.  We might first have a list of courses and instructors, as shown below.  (Note that, for simplicity's sake, we are making the assumption that there is only one section of each course and, therefore, that we can use it as the primary key.)  In this case, the instructor Adams has not yet been assigned to a course and the Computers course has no instructor assigned yet. Course Instructor Math Smith Science Jones NULL Adams Computers NULL Now, let's assume that we have a table containing course registrations for students.  Again, for simplicity's sake, we'll assume that each student must take one and only one course.  Every student must take a class, so we will include a NULL record for any student whose course registration information is unknown.  In this case, we're not sure what course Alan is taking. Student Course Ryan Math Betty Science Alan NULL If we perform an INNER JOIN, NULL values do not match each other.  For example, if we perform the following query: SELECT * FROM Instructors INNER JOIN Students ON Instructors.Course = Students.Course We'd get the following result: Course Instructor Student Math Smith Ryan Science Jones Betty The OUTER JOIN operations includes data from one or both tables that doesn't match data in the other table.  There are three types of OUTER JOINs: The LEFT OUTER JOIN includes rows from the table specified on the left side of the JOIN statement that don't match with rows from the table on the right side of the JOIN statement. The RIGHT OUTER JOIN includes rows from the table specified on the right side of the JOIN statement that don't match with rows from the table on the left side of the JOIN statement. The FULL OUTER JOIN includes rows from both tables that don't match data in the other table. If we performed the following query: SELECT * FROM Instructors LEFT OUTER JOIN Students ON Instructors.Course = Students.Course We'd get the following result set: Course Instructor Student Math Smith Ryan Science Jones Betty NULL Adams NULL Computers NULL NULL Similarly, the query: SELECT * FROM Instructors RIGHT OUTER JOIN Students ON Instructors.Course = Students.Course Would give us: Course Instructor Student Math Smith Ryan Science Jones Betty NULL NULL Alan And, finally, the query: SELECT * FROM Instructors FULL OUTER JOIN Students ON Instructors.Course = Students.Course Would yield: Course Instructor Student Math Smith Ryan Science Jones Betty Computers NULL NULL NULL NULL Alan NULL Adams NULL And that's a look at how NULL values are treated by the JOIN operation!

Join ,union and union all with null values in tables?

--demo of join,union and union all

NOTE: Tables having null values

create table join1(id int,name varchar(50))

insert into join1 values(1,'jana'),(2,'sundu'),(null,null),(4,'null')

create table join2(id int,name varchar(50))

insert into join2 values(1,'jana'),(2,null),(3,null),(4,'null')

select * from join1 j1

inner join

join2 j2 on

j1.id=j2.id

Result

id            name    id            name

1              jana       1              jana

3              sundu   3              NULL

4              null         4              null

--union

select * from join1 j1

union

select * from join2 j2

--Note:union will give both matching rows and nonmatching rows only once

--i.e distinct values

Result

id    name

NULL  adi

1     jana

2     NULL

3     NULL

3     sundu

4     null

--union all

select * from join1 j1

union all

select * from join2 j2

--it gives all values from both tables

Result

id            name

1              jana

NULL     adi

3              sundu

4              null

1              jana

2              NULL

3              NULL

4              null

views in sql server?

Views are virtual tables that represent the result set of a select statement from one or more tables or other views. In most cases, that is unless a view is indexed, a view is basically a predefined query that is stored and executed whenever the view is referenced in a query. You can use a view to limit user access to sensitive information in the underlying table. For example, you can create a view that only includes a subset of columns in a table and give the user access to the view instead of the underlying table. You can also use a WHERE clause to limit the rows of data that are returned, ensuring the user can see only specific rows. There are a few limitations you need to be aware of when creating a view. A SELECT statement in a view cannot include any of the following: A COMPUTE or COMPUTE BY clause. An ORDER BY clause without also including the TOP clause. The workaround for this is to select the top 100 percent, as in the following query: SELECT TOP 100 PERCENT FROM <table> ORDER BY <columns>. The INTO keyword used to create a new table. The OPTION clause. A reference to a temporary table or table variable. The example in Listing 1 creates a view that shows the name and department description of all the employees in a single department. As you can see in the example, we are able to use the view to hide the employee's social security number. We are also able to provide more user-friendly column names by using an alias. The WITH ENCRYPTION option prevents the view definition from being displayed. You should be careful when using the encryption option because you will not be able to retrieve the definition from the database if you need it. Example 1. Sample Code Used to Create an Encrypted View USE AdventureWorks2008 GO CREATE TABLE Employee (EmpID int NOT NULL CONSTRAINT PK_EMP PRIMARY KEY CLUSTERED, EmpFirstName Varchar(50), EmpLastName Varchar(50), EmpSSN Varchar(9), DepartmentID int) GO CREATE TABLE Department (DepartmentID int NOT NULL CONSTRAINT PK_DEPT PRIMARY KEY CLUSTERED, DepartmentDscr Varchar(50)) GO CREATE VIEW vMarketingEmployees WITH ENCRYPTION AS SELECT dbo.Employee.EmpFirstName AS FirstName, dbo.Employee.EmpLastName AS LastName, dbo.Department.DepartmentDscr AS Department FROM dbo.Department INNER JOIN dbo.Employee ON dbo.Department.DepartmentID = dbo.Employee.DepartmentID WHERE dbo.Department.DepartmentID = 1 Let's say you found out that the Marketing department is actually DepartmentID 2 instead of 1. You can use the ALTER VIEW statement shown in Listing 2 to make the changes. While making the changes, you also decide to remove the encryption option. All you have to do to remove the encryption is not to specify the option when running the ALTER VIEW statement. Example 2. Syntax Used to Alter an Existing View USE AdventureWorks2008 GO ALTER VIEW vMarketingEmployees AS SELECT dbo.Employee.EmpFirstName AS FirstName, dbo.Employee.EmpLastName AS LastName, dbo.Department.DepartmentDscr AS Department FROM dbo.Department INNER JOIN dbo.Employee ON dbo.Department.DepartmentID = dbo.Employee.DepartmentID WHERE dbo.Department.DepartmentID = 2 To remove a view from the database, all you have to do is issue the DROP VIEW statement followed by the view name, as shown in Listing 3. Example 3. Syntax to Remove a View from the Database DROP VIEW vMarketingEmployees 1. Partitioned Views Distributed partitioned views are those that reference data across multiple servers and combine the data to the user as a single result set. You can use distributed partitioned views to form a federation of database servers, which are separately managed servers used to spread the processing load for a single application across multiple servers. Listing 4 shows an example of a distributed partitioned view. NOTE You can create a local partitioned view by referencing only tables on the same server; however, creating partitioned tables is the preferred method for locally partitioning data. Example 4. Common Syntax Used in a Distributed Partitioned View CREATE VIEW vDistributedSample AS SELECT col1, col2, col3 FROM Server1.DBName.dbo.TableName UNION ALL SELECT col1, col2, col3 FROM Server2. DBName.dbo.TableName UNION ALL SELECT col1, col2, col3 FROM Server3.DBName.dbo.TableName 2. Updateable Views You can use a view to insert, update, and delete data in the underlying tables as long as certain conditions are met: All the columns being modified must be in the same base table. The columns must also directly reference the base table; you cannot modify computed columns or columns that are derived from or affected by aggregate functions. If the WITH CHECK option is specified, as shown in Listing 5, the view cannot be updated in any way that would cause the updated record to disappear from the result set. For example, given the view created in Listing 5, you could not run an UPDATE statement to set the DepartmentID = 2. Example 5. Syntax to Create an Updatable View Using the WITH CHECK Option USE AdventureWorks2008 GO --Drop the view if it currently exists IF OBJECT_ID('dbo.vMarketingEmployees', 'V') IS NOT NULL DROP VIEW dbo.vMarketingEmployees; GO --Create a view using the WITH CHECK option CREATE VIEW vMarketingEmployees AS SELECT dbo.Employee.EmpFirstName AS FirstName, dbo.Employee.EmpLastName AS LastName, dbo.Department.DepartmentID, dbo.Department.DepartmentDscr AS Department FROM dbo.Department INNER JOIN dbo.Employee ON dbo.Department.DepartmentID = dbo.Employee.DepartmentID WHERE dbo.Department.DepartmentID = 1 WITH CHECK OPTION If you look at Figure 1, you can see that trying to change DepartmentID fails, since it will violate the CHECK OPTION constraint, but changing Department is successful. Figure 1. Results of updating a view that uses the WITH CHECK option 3. Indexed Views If you have a process-intensive view that is run often, you can improve performance and reduce the processing time needed to execute the view by creating an index on the view. The first index you create on a view must be a unique clustered index, which causes the result set of the view to be stored in the database. Thereafter, you can create nonclustered indexes. Listing 6 shows an example of creating an indexed view. Notice the WITH SCHEMABINDING clause in the CREATE VIEW statement. If you plan to create an index on a view, then you must first create the view using that clause, which prevents any changes to the underlying tables referenced by the view that would affect the view definition. An indexed view is essentially a stored result set returned by the view, so SQL Server does not have to process the query each time the view is referenced. By creating the view WITH SCHEMABINDING, SQL Server can ensure that no underlying changes to the data will invalidate the stored results. There are several other requirements that must be met in order to create an indexed view. For the specific requirements, refer to the topic "Creating Indexed Views" in SQL Server Books Online. Example 6. Syntax to Create an Indexed View USE AdventureWorks2008 GO CREATE VIEW vEmployees WITH SCHEMABINDING AS SELECT dbo.Employee.EmpFirstName AS FirstName, dbo.Employee.EmpLastName AS LastName, dbo.Department.DepartmentID, dbo.Department.DepartmentDscr AS Department FROM dbo.Department INNER JOIN dbo.Employee ON dbo.Department.DepartmentID = dbo.Employee.DepartmentID GO --Create an index on the view CREATE UNIQUE CLUSTERED INDEX IDX_vEmployee_Dept ON vEmployees (DepartmentID); GO Indexed views are best suited for situations where the underlying data is rarely updated; because as the data is updated in the base tables, the data must also be updated in the indexed view to reflect the changes. If the data is frequently updated, maintaining the index could actually lead to performance issues instead of performance gains. There are many caveats to using indexed views that could lead to an administrative headache, so you shouldn't go around creating indexes on all your views. However, there are certain situations, especially in a data warehouse environment, where indexed views can provide an enormous performance benefit. You can create an indexed view in any edition of SQL Server 2008, but you receive some extra benefits when you are running the Enterprise Edition. In the Enterprise Edition, the query optimizer can automatically take advantage of an index created on a view, even if the view is not specifically referenced in a query.

self join in sql server?

 »

SQL SERVER – 2005 Explanation and Example – SELF JOIN

June 3, 2007 by pinaldave

A self-join is simply a normal SQL join that joins one table to itself. This is accomplished by using table name aliases to give each instance of the table a separate name. Joining a table to itself can be useful when you want to compare values in a column to other values in the same column. A join in which records from a table are combined with other records from the same table when there are matching values in the joined fields. A self-join can be an inner join or an outer join. A table is joined to itself based upon a field or combination of fields that have duplicate data in different records. The data-type of the inter-related columns must be of the same type or needs to cast them in same type.

When all of the data you require is contained within a single table, but data needed to extract is related to each other in the table itself. Examples of this type of data relate to Employee information, where the table may have both an Employee’s ID number for each record and also a field that displays the ID number of an Employee’s supervisor or manager. To retrieve the data tables are required to relate/join to itself.

Another example which can be tried on SQL SERVER 2005 sample database AdventureWorks is to find products that are supplied by more than one vendor. Please refer the sample database for table structure.

--SELF JOIN

CREATE TABLE Employee246(
EmployeeID INT PRIMARY KEY,
Name NVARCHAR(50),
ManagerID INT
)

INSERT INTO Employee246
SELECT 1, 'Mike', 3
UNION ALL
SELECT 2, 'David', 3
UNION ALL
SELECT 3, 'Roger', NULL
UNION ALL
SELECT 4, 'Marry',2
UNION ALL
SELECT 5, 'Joseph',2
UNION ALL
SELECT 7, 'Ben',2

select * from Employee246

select e2.Name,e1.Name as manager from Employee246 e1
inner join
Employee246 e2
on
e2.EmployeeID=e1.ManagerID

Primary key and unique ikey and difference between them?

A UNIQUE constraint is similar to PRIMARY key, but you can have more than one UNIQUE constraint per table.

When you declare a UNIQUE constraint, SQL Server creates a UNIQUE index to speed up the process of searching for duplicates. In this case the index defaults to NONCLUSTERED index, because you can have only one CLUSTERED index per table.

* The number of UNIQUE constraints per table is limited by the number of indexes on the table i.e 249 NONCLUSTERED index and one possible CLUSTERED index.

Contrary to PRIMARY key UNIQUE constraints can accept NULL but just once. If the constraint is defined in a combination of fields, then every field can accept NULL and can have some values on them, as long as the combination values is unique.