7.2 Filtering and sorting data

SQL filtering and sorting are essential skills for querying databases effectively. The WHERE clause allows you to filter rows based on specific conditions, using operators like =, <>, and BETWEEN. You can combine conditions with AND, OR, and NOT for complex queries.

Sorting results is done using the ORDER BY clause, which arranges data based on specified columns. You can sort in ascending or descending order, and even sort by multiple columns. Understanding NULL handling is crucial for accurate filtering and sorting in SQL.

Filtering Data

Conditional Filtering with the WHERE Clause

Top images from around the web for Conditional Filtering with the WHERE Clause

• 

  sql - Clarification of Logical Operators in a PostGIS Database - Geographic Information Systems ... View original

  Is this image relevant?

• 

  sql - which runs first:joins or where clause - Stack Overflow View original

  Is this image relevant?

• 

  sql - Clarification of Logical Operators in a PostGIS Database - Geographic Information Systems ... View original

  Is this image relevant?

• 

  sql - which runs first:joins or where clause - Stack Overflow View original

  Is this image relevant?

1 of 2

Top images from around the web for Conditional Filtering with the WHERE Clause

• 

  sql - Clarification of Logical Operators in a PostGIS Database - Geographic Information Systems ... View original

  Is this image relevant?

• 

  sql - which runs first:joins or where clause - Stack Overflow View original

  Is this image relevant?

• 

  sql - Clarification of Logical Operators in a PostGIS Database - Geographic Information Systems ... View original

  Is this image relevant?

• 

  sql - which runs first:joins or where clause - Stack Overflow View original

  Is this image relevant?

1 of 2

• Use the WHERE clause to filter rows based on specified conditions
• Place the WHERE clause after the FROM clause in a SELECT statement
• Specify the condition using comparison operators (=, <>, >, <, >=, <=)
• Combine multiple conditions using logical operators
  • AND returns true if all conditions are true
  • OR returns true if at least one condition is true
  • NOT negates the result of the condition
• Use parentheses to control the order of evaluation for complex conditions

Range and Membership Filtering

• Filter values within a range using the BETWEEN operator
  • Syntax:

    column_name BETWEEN value1 AND value2

  • Inclusive of both value1 and value2 (greater than or equal to value1 and less than or equal to value2)
• Check for membership within a set of values using the IN operator
  • Syntax:

    column_name IN (value1, value2, ...)

  • Equivalent to multiple OR conditions
• Filter based on pattern matching using the LIKE operator
  • Use wildcards to represent characters

    • %

      matches any sequence of zero or more characters

    • _

      matches any single character
  • Syntax:

    column_name LIKE 'pattern'

    (case-sensitive) or

    column_name ILIKE 'pattern'

    (case-insensitive)
  • Examples:

    • name LIKE 'A%'

      matches names starting with 'A'

    • email LIKE '%@example.com'

      matches emails ending with '@example.com'

Handling NULL Values

• Check for NULL values using the IS NULL operator
  • Syntax:

    column_name IS NULL

• Check for non-NULL values using the IS NOT NULL operator
  • Syntax:

    column_name IS NOT NULL

• NULL represents an unknown or missing value
• Comparison operators (=, <>, etc.) do not work with NULL values
• Use IS NULL or IS NOT NULL to explicitly check for NULL values

Sorting Results

Ordering Query Results

• Use the ORDER BY clause to sort the result set based on one or more columns
• Place the ORDER BY clause after the WHERE clause (if present) in a SELECT statement
• Specify the column(s) to sort by, separated by commas
• Optionally, specify the sort direction for each column using ASC (ascending, default) or DESC (descending)
  • Syntax:

    ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...

• Sorting is performed in the specified order (column1, then column2, etc.)

Sorting Order and NULL Values

• NULL values are considered the lowest values when sorting in ascending order
• NULL values are considered the highest values when sorting in descending order
• Examples:

  • SELECT * FROM employees ORDER BY salary DESC

    sorts employees by salary in descending order

  • SELECT * FROM products ORDER BY category ASC, price DESC

    sorts products by category in ascending order, and within each category, sorts by price in descending order