Selecting Rows in SQL


Operators

An operator is a symbol that computes a value from one or more other values, called operands.

  • arithmetic operators compute numeric values from numeric operands
  • comparison operators computer logical values TRUE or FALSE
    • may be numeric, character, or other data types
  • logical operators compute logical values from logical operands
  • A unary operator has one operand
  • A binary operator has two operands
    • most operands are binary
  • logical operator is not unary
  • arithmetic operator can be unary or binary

Common Operators

TypeOperatorDescriptionExampleValue
Arithmetic+Adds two numeric values4 + 37
Arithmetic- (unary)Reverses the sign of one numeric value-(-2)2
Arithmetic- (binary)Subtracts one numeric value from another11 - 56
Arithmetic*Multiplies two numeric values3 * 515
Arithmetic/Divides one numeric value by another4 / 22
Arithmetic% (modulo)Divides one numeric value by another and returns
the integer remainder
5 % 21
Comparison=Compares two values for equality1 = 2FALSE
Comparison!=
<>
Compares two values for inequality1 != 2
1 <> 2
TRUE
Comparison<Compares two values with <2 < 2FALSE
Comparison<=Compares two values with ≤2 <= 2TRUE
Comparison>Compares two values with >'2019-08-13' > '2021-08-13'FALSE
Comparison>=Compares two values with ≥'apple' >= 'banana'FALSE
LogicalANDReturns TRUE only when both values are TRUETRUE AND FALSEFALSE
LogicalORReturns FALSE only when both values are FALSETRUE OR FALSETRUE
LogicalNOTReverses a logical valueNOT FALSETRUE

Expressions

An expression is a string of operators, operands, and parentheses that evaluates to a single value.

  • operands can be column names or fixed values
  • value can be any data type
  • e.g., Salary > 34000 AND Department = 'Marketing'
  • operators are evaluated in order of operator precedence
  • expressions may evaluate to NULL

Operator Precedence

PrecedenceOperators
1- (unary)
2^
3*     /     %
4+     - (binary)
5=     !=     <     >     <=     >=
6NOT
7AND
8OR

SELECT Statement

The SELECT statement selects rows from a table.

  • has a select clause and a from clause
    • FROM clause specifies the table from which rows are selected
    • SELECT clause specifies expressions separated by commas
  • returns a set of rows, called the results table

Syntax

-- SELECT with expressions

SELECT Expression1, Expression2, …
FROM TableName;

— SELECT with column names
SELECT Column1, Column2, …
FROM TableName;

— SELECT with asterisk
SELECT *
FROM TableName;

LIMIT Clause

MySQL has a LIMIT clause that limits the number of rows returned by a SELECT statement.

Example

SELECT *
FROM City
LIMIT 100;

WHERE Clause

A condition is an expression that evaluates to a logical value.

  • A SELECT statement has an optional WHERE clause that specifies a condition for selecting rows
    • follows the FROM clause
  • can use % as a wildcard

Syntax

SELECT Expression1, Expression2, ...
FROM TableName
WHERE Condition;