QA Practices

LO1: Purpose of Union and Union All LO2: Difference between Union and Union All LO3: Difference between Joins and Union Prerequisite: Joins Union and Union All is used to combine the result set of two or more queries. To combine two results set, the column list in the select columns, data types, and order of the columns need to be same. Let’s say we have the two table tblcustomer1 and tblcustomer2 with the identical columns in each table. To combine these two result set into one we can use the Union All . select * from tblcustomer1 Union All select * from tblcustomer2 In the above screen, all the rows combined into one result set. Now let’s try to use the Union only and observe the result set. select * from tblcustomer1 Union select * from tblcustomer2 In the above screen, the duplicate row is included only once and the result also gets sorted. Note: In the case of Union, Duplicate rows only get removed from the re...

LO1: COALESCE() function with example Prerequisite: Replacing NULL in SQL COALESCE function return the first NON NULL value. Let’s say we have a table called tblPersons which contains data as below. In this table, some persons have the first name, some have the middle name, some have the last name or some have the all the three values. So the COALESCE function will fetch the first NON NULL values from the table i.e. this function will look into the each column value if it finds NULL then it will move to the next column to get the value. This process will continue until all the columns in the select list are traversed. select Id, COALESCE(FirstName, MiddleName, LastName) as Name from tblPersons If all the columns contain the NULL value then that row will be included in the result. That’s all for today. Thank You.

LO1: Replacing NULL with ISNULL function LO2: Replacing NULL with COALESCE function LO3: Replacing NULL with Case Statement Prerequisite: Self Joins In the previous session, we have found the employee’s managers using the Left Outer Join and we get the NULL value for the employee who does not have the manager assign or we can say he is the BOSS! So now we are trying to replace this NULL value with the specified text using different methods. Replacing NULL with ISNULL function The ISNULL function accepts two parameters. First is the expression and second are the replacement value i.e. the value which is replaced by NULL. If the expression you have passed in ISNULL function returns the NULL then NULL will be replaced by the given value as per the second parameter. Now applying the ISNULL function in the Left Join to replace the NULL value with the text saying “BOSS”. select e.Name as Employee, ISNULL(m.Name,'BOSS') as Manager ...