• UNION ALL and UNION
• LIKE and ILIKE
• NOT and ! and <>
• DATE_PART() and DATE_TRUNC()

UNION ALL and UNION

UNION
The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type. With UNION, only distinct values are selected.

UNION ALL
The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values.

The difference between Union and Union all is that Union all will not eliminate duplicate rows, instead it just pulls all rows from all tables fitting your query specifics and combines them into a table.

A UNION statement effectively does a SELECT DISTINCT on the results set. If you know that all the records returned are unique from your union, use UNION ALL instead, it gives faster results.

UNION removes duplicates, whereas UNION ALL does not.

Of course, if you need data returned without duplicates then you must use UNION, depending on the source of your data.

UNION – results in distinct records

while

UNION ALL – results in all the records including duplicates.

Both are blocking operators and hence I personally prefer using JOINS over Blocking Operators(UNION, INTERSECT, UNION ALL etc. ) anytime.

To illustrate why Union operation performs poorly in comparison to Union All checkout the following example.

LIKE and ILIKE

here

The predicates LIKE and ILIKE are used to search for strings that match
a given pattern, so you can search or for a single word (or string) in a
long text field.

LIKE is case sensitive, ILIKE is case insensitive.

Two special characters are used to construct the comparison.

• % The percent sign indicates that any number of characters (or none
  at all) can be located in the place occupied by the %.
• _ The underscore sign indicates that any single character can be
  located in the place occupied by the _.

Comparing empty strings always evaluates TRUE.

The result is FALSE if the expression contains a NULL.

If you want to find a string containing a percent sign or the underscore
character (for example, “% discount”), use an ESCAPE character before
the % or _. The ESCAPE character indicates that the % or _ is to be
taken literally. The syntax is shown below. (The expression in the
syntax selects “_T”.):

SELECT...
WHERE... LIKE '%+_T%' ESCAPE '+'

You can choose what character to use as the escape character—this
example uses the plus sign (+), which precedes the underscore. Example 3
uses the equals sign (=) and then the hash (#) as ESCAPE characters.

Note: “The ANSI ‘92 standard states that the expression and pattern
arguments for LIKE can be string value expressions. However, the
Kognitio implementation limits the pattern argument to be a constant.”

NOT ,! ,<>

Technically there is no difference between != and <>. Both of them work the same way and there is absolutely no difference in terms of performance or result.

In terms of performance , the two queries almost the same. if you can check the actual execution plan in SQL Server, there is no difference of the two query. NOT is a negation and the other (<>) is an operator used for comparison.

NOT is a negation, <> is a comparison operator, they are both ISO standard. And have no performance difference for your example.

DATE_PART() and DATE_TRUNC()

The DATE_PART() function pick a specified field from a TIMESTAMP or date value, while DATE_TRUNC() rounds off the value to the specified precision. The DATE_TRUNC() function truncates (trim) an INTERVAL or TIMESTAMP value on the basis of a specified date part.

NOT IN vs SQL NOT EXISTS vs SQL LEFT JOIN vs SQL EXCEPT

--- NOT INT
SELECT Cat_ID
FROM Category_A  WHERE Cat_ID NOT IN (SELECT Cat_ID FROM Category_B)
GO
-- NOT EXISTS
 
SELECT A.Cat_ID
FROM Category_A A WHERE NOT EXISTS (SELECT B.Cat_ID FROM Category_B B WHERE B.Cat_ID = A.Cat_ID)
GO
-- LEFT JOIN
 
SELECT A.Cat_ID
FROM Category_A A 
LEFT JOIN Category_B B ON A.Cat_ID = B.Cat_ID
WHERE B.Cat_ID IS NULL
GO
-- EXCEPT
 
SELECT A.Cat_ID
FROM Category_A A 
EXCEPT 
SELECT B.Cat_ID
FROM Category_B B
GO

using the SQL NOT EXISTS or the LEFT JOIN commands are the best choice from all performance aspects. We tried also to add an index on the joining column on both tables, where the query that uses the EXCEPT command enhanced clearly and showed better performance, besides the SQL NOT EXISTS command that still the best choice overall.