UNION ALL and
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 command is equal to the
UNION command, except that
UNION ALL selects all values.
The difference between
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.
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
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
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.
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.