NULLIF Function in SQL

NULLIF Function

This SQL Server tutorial explains how to use the NULLIF function in SQL Server (Transact-SQL) with syntax and examples.

Description

In SQL Server (Transact-SQL), the NULLIF function compares expression1 and expression2. If expression1 and expression2 are equal, the NULLIF function returns NULL. Otherwise, it returns the first expression which is expression1.

Syntax

The syntax for the NULLIF function in SQL Server (Transact-SQL) is:

NULLIF( expression1, expression2 )

Parameters or Arguments

expression1, expression2

The expressions that will be compared. Values must be of the same datatype.

What is the Default Datatype of NULL?

What is the Default Datatype of NULL?

NULL is undefined and every column can have NULL values except columns with timestamp datatype (which represent NULL values differently). There is a simple method to know the default datatype of NULL values.

First, let us create a table with SELECT INTO TABLE Syntax and then we will try to find out the datatype of the column where we have stored NULL. Please note that we are not providing any datatype for the NULL value.

SELECT NULL AS col

INTO #TempTable

Now let us look at the structure of the table and see what datatype is assigned to the column named col in the table #TempTable

EXEC tempdb..sp_columns #TempTable

Now please look at the column named Type_Name in the resultset. You will find that the default datatype is an Integer.

So, theoretically, I agree that it is difficult to come up with the datatype of the NULL value, but in SQL Server the default datatype of the NULL is an Integer.

Database normalization

Database normalization 

Database normalization is a technique to organize the contents of databases. Without normalization, database systems can be inaccurate, slow, and inefficient. The community of database professionals developed a series of guidelines for the normalization of databases. Each 'level' of normalization is referred to as a form, and there are 5 forms, total. First normal form is the lowest level of normalization, up to fifth normal form, which is the highest level of normalization.

• First Normal Form (1NF): The most basic level of data normalization, first normal form requires the elimination of all duplicate columns in a table, and also requires the creation of separate tables for related data, and identification of each table with a primary key attribute.

• Second Normal Form (2NF): Meets all the requirements of first normal form, and creates relationships between tables using foreign keys.

• Third Normal Form (3NF): Meets all the requirements of second and first normal forms, and removes all columns that are not dependent upon the primary key. Third normal form also removes all derived attributes, such as age.

• Fourth Normal Form (4NF): Fourth normal form adds one additional requirement, which is the removal of any multi-valued dependencies in relationships.

• Fifth Normal Form (5NF): Fifth normal form is a rarer form of normalization, in which case join dependencies are implied by candidate keys (possibly primary key values).

In the reality of database development, getting to 3NF is the most important jump. 4NF and 5NF are a bit more of a luxury (and sometimes a nuisance) in database development, and are rarely seen in practice. If you're struggling with the concepts, or remembering the first three forms, there is a simple relation. "The key, the whole key, and nothing but the key.", which relates to 1NF, 2NF, and 3NF.

The Benefits of Normalization

Now, without venturing too far into database theory, let's simply focus on the benefits of normalization. As the data progresses through the normalization forms, it becomes cleaner, better organized, and faster. Now, with a small database that has only 5 tables and 100 rows of data, this won't be readily apparent. However, as the database grows, the effects of normalization will become much more apparent with regards to speed and maintaining data integrity. However, there are some situations in which normalization doesn't make sense, such as when normalizing the data will create excessively complex queries required to return the data.

Understand Data Types IN SQL

Understand Data Types IN SQL

In SQL, typically each table column has an associated data type. Text, Integer, VarChar, Date, and more, are typically available types for developers to choose from.

• MySQL Data Types
• Oracle Data Types
• SQL Server Data Types

When developing, make sure you choose the proper data type for the column. Dates should be DATE variables, numbers should be a numeric type, etc. This becomes especially important when we deal with a later topic: indexing; but I'll demonstrate an example of poor knowledge of data types below:

SELECT employeeID, employeeName

FROM employee WHERE employeeID = 5142875;

Looks fine based on what we currently know, correct? However, what if employeeID is actually a string. Now we've got a problem, because the DBMS might not find a match (because string data types and integers are different types).

Therefore, if you're using indexing, you'll probably be perplexed as to why your query is taking forever, when it should be a simple index scan. This is the reason that developers need to pay special attention to data types and their applications. Non-key attributes which are IDs are often string types, as opposed to integers, because of the increased flexibility that is granted. However, this is also a trouble area for junior developers, who assume that ID fields will be integers.

Understand Joins in SQL

Understand Joins in SQL

Single table SQL select statements are rather easy to write. However, business requirements often dictate that more complex queries must be written. For example, "find all orders for each customer, and display the products for each order". Now, in this particular situation, it would be likely that there is a customer table, an order table, and an order_line table (the last would be to resolve a possible many-to-many record relationship). For those who are slightly more familiar with SQL, it is readily apparent that a table join, actually, two table joins will be required for this query. Let's look at some sample code.

SELECT customer.customerID, order.order_id, order_line.order_item FROM customer     INNER JOIN order         ON customer.customerID = order.customerID     INNER JOIN order_line         ON order.orderID = order_line.orderID;

Alright, simple enough. For those who don't know, the code above is an inner join. More specifically, the code above is an equi-join.
Let's define the various types of joins.

Inner Joins: The basic purpose of inner joins is to return matching records.

Outer Joins: Outer joins do not require each record to have a matching record.

• Left outer join: A left outer join of tables A and B will return all matching records of A and B, as well as any non-matched records from the left table, in this case, A.
• Right outer join: A right outer join of tables A and B will return all matching records of A and B, as well as any non-matched records from the right table, in this case, B.
• Full outer join: A full outer join of tables A and B will return all matching records of A and B, as well as any non-matched records from both tables.

Self Joins

·         There is one last type of join that must be considered, which is a self join. A self join is merely a join from a table to itself.

EMPLOYEE TABLE -EmployeeName -SupervisorID

·         In this situation, in order to find which employees are supervised by a given employee, a self join would be required.

·         Hopefully this clarifies the basic tenets of joins, as they are one of the primary features of SQL that makes it such a powerful database language. Make sure you use the proper join for your given situation.