Monday, October 23, 2017

Conditional OR joins in SQL query returns duplicate rows

Scenario: Whenever I have a particular OR condition join in my query, I get a duplicated record. The case is when the two join conditions before and after OR are satisfied.

SELECT A.Column1,A.Column2, Name, Address FROM A, B
WHERE (
(A.COLUMN1 = B.COLUMN1) OR (A.COLUMN2  = B.COLUMN2)
)

Cause: Imagine there are rows in A and B that satisfy the two join conditions used in OR, In such a scenario the database will return two rows with same value.


Here as per the query, for row 'Athul' in Table A, both the join conditions match with B and hence will return below output resultset,



As you can see in the result above, 'Athul' is resulted twice.  However for the second recrod

Solution: This can be avoided by using a LEFT join instead of an OR join condition. Do not consider using DISTINCT as it is a fairly cost consuming alternative.

Friday, October 13, 2017

Oracle Dual Table alternative equivalent in Microsoft SQL Server

Here is another post addition to the Oracle to Microsoft SQL Server series, This time we discuss about the DUAL table feature that is available in Oracle. However SQL server doesn't support queries on DUAL table as it is not present in the database. 

SELECT 1 FROM DUAL  returns invalid object name error in MSSQL Server.  -

Msg 207, Level 16, State 1, Line 1
Invalid column name 'SYSDATETIME'.

Dual object invalid in MS SQL Server.

Dual table is a one row table that Oracle provides where you can do one row calculations, use to display SYSDATE, test functions with default values etc. eg:- 

SELECT SYSDATE FROM DUAL - returns the system date from the database server. 
SELECT (9*50) FROM DUAL; - returns 450
Solution or Alternative to DUAL in MS SQL server is as below,  

In MSSQL, Any function, calculation, string after SELECT works, Please find the below examples along with screenshot.

SELECT GETDATE()  - returns the system date.
SELECT (9*50) - returns 450 
SELECT  'Hello World' 

Dual alternative in SQL Server.

An alternative to this syntax is to create a DUAL table in MS SQL server with the Dummy column and value 'X' under it. However you have to make sure you that all the users have access this to table to be used just as in Oracle database.

Tuesday, October 10, 2017

Performance difference between != and <> for NOT EQUAL filters in MSSQL / Oracle

Question: Is there a considerable performance improvement when using <> over the != operation in Microsoft SQL Server or Oracle Database ?

Answer: The answer is 'NO'. I had from somewhere heard the same that the <> operator performs better than the != operator. The < > operator for NOT EQUAL to can be preferred as the <> syntax is ANSI compliant unlike the != operator. Chances are when you migrate the same code to a different database, the <> operator is more likely to work without any compilation errors. 

So use <> operator instead of != as it is ANSI compliant and will help you reduce probable effort if a database migration happens. 

References: Stack Overflow




Tuesday, September 26, 2017

CREATE TABLE from SELECT query in Microsoft SQL Server


Scenario: Oracle has spoiled us so much, We recently came across a scenario where we wanted to have the result set of a SQL query to be saved as a table. In Oracle we have the following syntax to achieve the same,


CREATE TABLE STUDENTS AS SELECT STUD_ID, STUD_NAME FROM STUDENT;

This syntax would create me a table instantly, which I would be able to refer elsewhere. The same feature is available in Microsoft SQL Server as well, just that the syntax is different.  

Resolution: The SQL server equivalent of the above query would be as below, 


SELECT A.* INTO STUDENTS FROM( SELECT STUD_ID, STUD_NAME FROM STUDENT) A
syntax: 

SELECT A.* INTO Insert_Table_Name FROM 

Your query here.
) A

Note that you need not create the table upfront before running this script. The query creates the table and it's structure based on the result set.

We hope this came of help to you. For more such posts relating Oracle and Microsoft SQL Server check the label - Oracle to SQL Server . 

Monday, September 25, 2017

Oracle NVL equivalent in MS SQL Server - COALESCE

Oracle to Microsoft SQL Server
As I am new to the Microsoft SQL Server, I have decided to do a series on Oracle to SQL Server comparison. So here is one new keyword which I learned the other day.

Scenario: I had this scenario where I am supposed to show an email address of an insurance dependent, if this dependent email id is not updated then we should fetch the email id of the parent. If in Oracle I would have gone for the NVL function or a CASE statement to achieve the same. If it just comparing maybe two to three columns then a CASE statement would suffice. What if we had more than 10 columns in a way that we have to show data if one of these 10 columns are having data. 

Solution: In this scenario I could go for the COALESCE function in Microsoft SQL Server as the it doesn't provide the NVL function and anyways the COALESCE function performs better than NVL in Oracle and makes your code simpler. 

eg: Imagine we have a CUSTOMER table with 4 email address columns, EMAIL_1, to EMAIL_10.  we have to return the first available data among these columns checking serially. If in Oracle we could have gone for the below syntax,


SELECT NVL(EMAIL_1,NVL(EMAIL_2,(NVL(EMAIL_3, NVL(EMAIL_4,'Not available,),,'Not available,),'Not available,),'Not available,) EmailId
FROM CUSTOMER

As you can see the code is messy and may take time to understand. But SQL Server provides you a function for such a scenario called the COALESCE function, which does the same for you. The above result in COALESCE would look like below, 

SELECT COALESCE(EMAIL_1,EMAIL_2,EMAIL_3,EMAIL_4) EmailId FROM CUSTOMER.

Use of the function makes way for simpler and clean code. The COALESCE function converts your code to a CASE function and executes the same. The difference with ISNULL function is that the ISNULL function is executed only once where as COALESCE is executed until a non NULL argument is reached. 

Hope this came of help to you. Happy coding.