SQL - Filtering Rows

Commands Covered


  • SELECT
  • FROM
  • WHERE
  • ALL / AND / ANY / etc


Where Operators

To select specific rows, you need to apply a bit of logic with the where statement. This is the general formatting of such a query.

SELECT 
  * 
FROM 
  example_table
WHERE
  column OPERATOR threshold


The following operators are valid in MySQL, along with the typical comparison operators (<, !=, etc).

ALL - every value matches
AND - multiple conditions must be true
ANY - any of the conditions can be true
BETWEEN - values within two bounds
EXISTS - self explanatory
IN - value is present within a list
LIKE - used for similar values with wildcards
NOT - combined with the other operators
OR - either condition may be true
IS NULL - doesn’t have data entered
UNIQUE - no duplicates


A few examples:

Return rows that contain dates outside of a specified range:

SELECT 
  * 
FROM
  example
WHERE
  hire_date NOT BETWEEN '1990-01-01' AND '2000-01-01';


Return row entries that only appear once:

SELECT UNIQUE
  gender
FROM 
  example;


Return rows that meet several conditions at once:

SELECT
  *
FROM
  example
WHERE
  last_name = 'Denis'
  AND (gender = 'M' OR gender = 'F');


For more information on using where statements on strings, dates, or null values, please view the related toolbox articles.

That’s all for now!



 Data Toolbox

Comments

Related Posts