www.SQLdemon.com    
  Home | Place like no other Learn | Things you should know Labs | Try what you know Stuff | Things you may need Blog | Latest things to discuss
 

Using the WHERE Clause

Filters become handy when you are looking forward for the specific results out of a SQL query, and the WHERE clause is one of the most commonly used filter to fulfil the purpose. The WHERE clause appear right after the FROM clause followed by the filtering condition. This works directly on the column names and alias cannot be used in this clause.

Syntax

SELECT Column_expression
FROM table
WHERE condition(s)
The WHERE clauses are capable of comparing values in Columns, arithmetic expressions and functions. Each WHERE clause consists of 3 elements.

  • Column Name
  • Comparison Condition
  • Column Name, Constant, or list of values


Comparison conditions

= Equality
<> Non equality
!= Non equality
< Less than
<= Less than or equal to
!< Not less than
> Greater than
>= Greater than or equal to
!> Not greater than
BETWEEN Between two specified values
IN Match against a list of values
LIKE Match a character pattern
IS NULL Is a NULL value


Comparing expression

The expression that is being compared to has to be given to match with the existing data type and format.

  • Character strings and date values are enclosed in single quotation marks.
  • Character values are case sensitive, and date values are format sensitive.
  • Default date format is DD-MON-YY


WHERE clause with Character

SQL Statement

SELECT *
FROM employees
WHERE first_name = 'Alexander';
Result

Result

Using Character with Where clause


WHERE clause with Numbers

SQL Statement

SELECT *
FROM employees
WHERE salary >= 6000;

Result

Using Numeric with Where clause


WHERE clause with Date

SQL Statement

SELECT *
FROM employees
WHERE hire_date = '29-JAN-00';

Result

Using date in Where clause