SQL Operators

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.

Table 18.1: SQL MATHEMATICAL OPERATORS

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

Table 18.2: SQL COMPARISON OPERATORS

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

 
Table 18.3: SQL LOGICAL OPERATORS

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.



Mastering Dreamweaver MX Databases
Mastering Dreamweaver MX Databases
ISBN: 078214148X
EAN: 2147483647
Year: 2002
Pages: 214

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net