Thursday, 11 July 2013

SQL – Where, Operators and alias

The SQL Statement

The WHERE Clause is used when you want to retrieve specific information from a table, i.e. filter the data based on conditions. These conditions are specified in the Where clause of SQL statement. For example, when you want to see the information about employees in class HR department only then you apply filter as
WHERE employee_dept = 'HR"
Thus the data is restricted during retrieval and hence query is faster to execute. WHERE clause can be used along with SELECT, DELETE, UPDATE statements.

The Where clause makes use of Comparison Operators and Logical Operators. Comparison operators are used to compare the column data with specific values in a condition. Comparison Operators are also used along with the SELECT statement to filter data based on specific conditions.

Comparison Operators

  1. = equal to
  2. <>, != is not equal to
  3. < less than
  4. > greater than
  5. >= greater than or equal to
  6. <= less than or equal to

There are three Logical Operators namely, AND, OR, and NOT. These operators compare two conditions at a time to determine whether a row can be selected for the output. When retrieving data using a SELECT statement, you can use logical operators in the WHERE clause, which allows you to combine more than one condition.

Logical Operators

  1. OR - For the row to be selected at least one of the conditions must be true.
  2. AND - For a row to be selected all the specified conditions must be true.
  3. NOT - For a row to be selected the specified condition must be false.

SELECT e.emp_name FROM emp_details e WHERE emp_name = 'Sally' OR emp_name = 'Harry'
In this statement - "e" denotes an alias for table emp_details.