SQL - COALESCE() Function

SQL - COALESCE() - This function plays a very important role in SQL because it will take any number of parameters, and return the first value encountered that isn't NULL. All the passed parameters into the function must have the same data type. If all parameters are NULL, COALESCE returns NULL. At least one of the null values must be a typed NULL.
COALESCE is ANSI standard function which is internally translated to a CASE expression and there is an advantage that we aware what to expect when using it. COALESCE is harder to spell, but at least it doesn't lead to incorrect.

SQL Syntax COALESCE ( expression [ ,...n ] ) Arguments Expression: Is an expression of any type. Return Types: Returns the data type of expression with the highest data type precedence.

Note: COALESCE requires at least minimum of two expressions. If all the passed parameters are not the same data-type then they get implicitly cast to an appropriate data-type using data-type order of precedence.

To understand the powerful features of COALESCE function; we are taking an example to explain it. In this example, we are going to create a temporary table to store the user comments on the various topics and their contact numbers.

USE tempdb GO ---- CREATE TEMP TABLE FOR COMMENTS CREATE TABLE #UserComments ( UserId Int, Topic varchar(200), Comments varchar(500), Phone1 Varchar(12), Phone2 Varchar(12), Phone3 Varchar(12) ) ---- insert data into comments table INSERT INTO #UserComments(UserId,Topic,Comments,Phone1,Phone2,Phone3) VALUES(1,'Population','Population could be the biggest problem for the world',1245679870,NULL,9852631450), (2,'Pollution','Now a day, people are suffering lots of disease due to Pollution',NULL,6758901243,NULL), (3,'Population','India and China is the biggest country for the Population',9321456235,NULL,NULL), (4,'Pollution','There are many type of the Pollution',NULL,NULL,8856321456) ----- pull the records from the comments table SELECT UserId,Topic,Comments, Phone1,Phone2,Phone3 FROM #UserComments; UserId Topic Comments Phone1 Phone2 Phone3 1 Population Population could be the biggest problem for the world. 1245679870 NULL 9852631450 2 Pollution Now a day, people are suffering lots of disease due to Pollution. NULL 6758901243 NULL 3 Population India and China is the biggest country for the Population. 9321456235 NULL NULL 4 Pollution There are many type of the Pollutions. NULL NULL 8856321456 ---- drop temp table DROP TABLE #UserComments;

Return first Non-able Value

On the basis of the User Comments table, we can get the any available contact number against any user Id because COALESCE function can take n-numbers of the arguments and will return the first not null able value from them as given below:

---- Pull the contact number for the user select UserId, Topic, ContactNumber=Coalesce(Phone1,Phone2, Phone3) from #UserComments UserId Topic ContactNumber 1 Population 1245679870 2 Pollution 6758901243 3 Population 9321456235 4 Pollution 8856321456

Before coalesce function is run, it checks what data types it is comparing. i.e. INT, INT, INT and DATETIME. It decides that for this function they should all be processed as DATETIME and implicitly converts them before it calls the function.

Using Coalesce to comma separated values

In the above table, we can use the coalesce function to get the comma separated values as given below:

---- declare local variable DECLARE @UserComments VARCHAR(1000) ----- get the vales into variable SELECT @UserComments = COALESCE(@UserComments,'') + Comments + ','  FROM #UserComments WHERE (Topic = 'Population') ----- pull value from variable SELECT @UserComments AS UserComments UserComments Population could be the biggest problem for the world.,India and China is the biggest country for the Population.,

Conclusion

Coalesce allows multiple items to be compared in one statement. COALESCE () most often appears within a very specific content, such as in a query or view or stored procedure. COALESCE is a powerful tool if you are returning numerous values to a user and want to substitute occurrences of NULL with values from a different column or with an expression.

SQL - Derived Tables

Derived tables is the most outstanding feature of SQL which provides a momentous help to the developers because they are always created on the fly with the help of SELECT and FROM statements in parenthesis and can also be used in queries such as Sub-Queries, Common Table Expressions, and Views. The amazing point is that no need to drop it because SQL Server is the responsible to create and populate the table in the memory which can be referenced by the outer Select query who created.

To simplify it, a derived table is best thought of as a table within a table that exists only as long as the outer query does. They can assumed as on the fly views also in SQL.

Drawback Points: A derived table only exists in the query in which it is created. So, derived tables are not actually part of the database schema because they are not real tables. SQL server will eliminate it forever from the memory when the outer query is finished. Reusable Options: If you want to re-use the output of the Derived table in the SQL query then you must need to be store its output into the table variable or temporary table because they are available till the end  of the query session.

Due to reside in the memory, it would be faster the temporary tables which are created in the temp database but in case of if you are fetching few records from the database. SQL server will eliminate it when the outer query is finished.

Explanation of Derived tables: Use of Derived tables are depend on the requirements and we are explaining some of them here. An employee table is used to store all the necessary information of the employees and department master is used to store department related details

USE TEMPDB GO SET NOCOUNT ON ---- Create table employee CREATE TABLE Employees ( EmpId Int NOT NULL, EmpName Varchar(20) NOT Null, EmpSalary money not null, DeptId Int Not Null, EmpAge Int Not Null ) GO ----- Insert values into table INSERT INTO Employees (EmpId, EmpName, EmpSalary, DeptId,EmpAge) VALUES (101, 'Ryan Arjun', 50000, 51, 23), (102, 'Kimmy Wang', 42000, 52, 29), (103, 'Bill Smith', 45000, 52, 35), (104, 'Rose White', 25000, 53, 21), (105, 'Mac Johnson', 30000, 51, 32)   GO ----- Output from the table select * from Employees; EmpId EmpName EmpSalary DeptId EmpAge 101 Ryan Arjun 50000 51 23 102 Kimmy Wang 42000 52 29 103 Bill Smith 45000 52 35 104 Rose White 25000 53 21 105 Mac Johnson 30000 51 32 ----- create another table department CREATE TABLE Department ( DeptID INT Not Null, DeptName Varchar(20) Not Null ) GO ---- Insert Values into table INSERT Department VALUES (51, 'Accounts'), (52, 'Sales'), (53, 'Marketing'), (54, 'Admin') GO ----- pull values from department table select * from Department; DeptID DeptName 51 Accounts 52 Sales 53 Marketing 54 Admin ---- pull values select e1.EmpId, e1.EmpName, e1.EmpSalary, dpt.DeptName from Department dpt ----- derived table inner join ( select EmpId, EmpName, DeptId, EmpSalary from Employees where EmpSalary>42000 and EmpName like 'R%' )E1 on dpt.DeptID=E1.DeptId EmpId EmpName EmpSalary DeptName 101 Ryan Arjun 50000 Accounts ---- DROP TABLES DROP TABLE Employees, Department

Referencing Multiple Derived Tables- As we are working with the views, the same way, you need to pull the required columns to put them into SELECT and FROM statements in parenthesis and join them together on the basis of data key relations. In this case, they are best way to provide on the fly views like structures.

Conclusion

Derived tables are the best option to avoid to create views which are not going to be used again and again. They provide a simple way to improve the performance of the SQL Query and SQL Server will take care of the Derived Tables because they stored in the memory and refreshed by the itself.