|
|
Earlier in this chapter, we introduced the AND and OR logical operators. Operators are symbols that represent mathematical, comparison, logical, and concatenation actions in an expression. Tables 18.1 through 18.3 list the SQL operators. Almost all systems support most SQL operators, but not all, so be sure to check your system documentation for more specific information.
Symbol | Representative of or Description | Notes |
---|---|---|
+ | Addition | |
- | Subtraction | |
- | Changes the sign of an operand | |
* | Multiplication | |
/ | Division | |
\ | Integer division | Not in MySQL |
^ | Exponentiation | Not in MySQL |
% | Returns the remainder of division by an integer | Not in MySQL; use MOD in Access |
Symbol | Representative of or Description | Notes |
---|---|---|
= | Equals | |
> | Is greater than | |
< | Is less than | |
>= | Is greater than or equal to | |
<= | Is less than or equal to | |
<> | Is not equal to | MySQL also recognizes the exclamation character |
(!).IS | Compares two object reference variables | |
LIKE | Compares string values by character |
Symbol | Representative of or Description | Notes |
---|---|---|
IN | Compares a value to a list of values | |
OR | Meets any one condition | MySQL also recognizes the double pipe (||). |
AND | Meets all conditions | MySQL also recognizes the double ampersand (&&). |
BETWEEN | Falls between two values and includes both comparison values | |
NOT | Negates the result of an expression | MySQL also recognizes the exclamation character (!) |
Note | An operand is a literal value, variable, reference, or function that's evaluated by an operator.The UNION statement isn't a statement in the same sense as the others we've discussed, although you'll use it in the same manner. The UNION statement is really an operator that combines records from two tables. Use the form: |
SELECT * | column1[, column2, ...] UNION [ALL] SELECT * | column1[, column2, ...]
Both SELECT statements must represent compatible tables or queries. That means the column order must be identical in both datasources. SQL doesn't care if the names are the same, but the datatypes must match, column per column. When corresponding datatypes aren't compatible, SQL selects the datatype that's most compatible with the data in both columns. We recommend you avoid allowing SQL to choose unless you have specific reasons to do so because SQL's decision isn't predictable.
By default, the UNION operator omits duplicate records. In this form, SQL sorts the form by the first column. This is the result of an internal sort necessary for SQL to omit duplicate records. You can include all records by adding the ALL predicate to the UNION clause.
A good use for a UNION query is combining similar tables. For instance, another branch might send you a new table full of data that you need to combine with your own. If the structure is the same, a UNION might be the most efficient solution.
There really isn't a good example for this in the Northwind sample database, but let's suppose an employee sends you a table with new customer data. You could re-enter the data, but because the table structure is the same, you can run the following query to combine both tables:
SELECT * FROM CustomersNew UNION SELECT * FROM Customers
Include the ALL predicate in the UNION clause in the form
UNION ALL
when you want to return all records, including duplicates. In addition, you can improve performance by using this predicate if you know there are no duplicates but a lot of data. The predicate omits the comparison step and, as a result, responds faster.
Warning | The results of a UNION query are read-only. |
|
|