UNION ALL
andUNION
LIKE
andILIKE
NOT
and!
and<>
DATE_PART()
andDATE_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
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.