🔵Filter Clauses

WHERE clause

Overview

The WHERE clause is used to filter records returned from a SELECT, UPDATE, or DELETE statement. It allows you to specify conditions that rows must meet to be included in the result set.

Syntax

SELECT columns 
FROM table
WHERE condition;

The basic syntax is to follow the SELECT and FROM clauses with a WHERE and a conditional expression.

Rules

• The WHERE clause appears after the FROM and before ORDER BY and GROUP BY clauses.

• The condition evaluates to True, False or Unknown for each row. Rows resulting in True are included.

• Conditions can use comparison, string, logical and other operators.

• Multiple conditions can be combined using AND, OR and NOT logical operators.

Operators

Some commonly used operators are:

• Comparison operators: =, !=, >, <, >=, <=

• BETWEEN ... AND

• IN (set of values)

• LIKE for pattern matching

• IS NULL to check for null values

Examples

SELECT *
FROM customers
WHERE age >= 25;

SELECT * 
FROM products 
WHERE price BETWEEN 10 AND 20;

SELECT *
FROM employees
WHERE last_name IN ('Smith', 'Jones');

SELECT *
FROM books
WHERE title LIKE '%Computer%'

Conclusion

The WHERE clause is an essential filtering component in SQL. It allows you to specify precise conditions to target only relevant rows and avoid entire table scans. This results in faster queries and reduced load on the database.

HAVING clause

Overview

The HAVING clause is used to filter groups of rows returned by the GROUP BY clause. It enables filtering based on aggregate functions like SUM(), COUNT(), AVG() etc.

Syntax

SELECT columns
FROM table
GROUP BY columns
HAVING condition;

The HAVING clause comes after GROUP BY and before ORDER BY.

Rules

• HAVING can only be used with a GROUP BY clause.

• The condition can use aggregate functions to evaluate against grouped rows.

• Logical operators like AND, OR can combine multiple conditions.

• WHERE filters rows before aggregation, HAVING filters groups after aggregation.

Operators

HAVING conditions can use the same operators as WHERE:

• Comparison operators like =, !=, >, <

• BETWEEN, IN

• Pattern matching with LIKE

• IS NULL

And aggregate functions like:

• SUM(), AVG(), COUNT(), MIN(), MAX()

Examples

SELECT department, SUM(salary) 
FROM employees
GROUP BY department
HAVING SUM(salary) > 100000;

SELECT customer_id, COUNT(order_id)
FROM orders
GROUP BY customer_id
HAVING COUNT(order_id) > 2;

Conclusion

The HAVING clause allows filtering of groups based on aggregate criteria after the GROUP BY grouping is done. This is more powerful than WHERE which cannot use aggregates. HAVING provides advanced filtering capabilities for aggregated data.

GROUP BY Clause

Overview

The GROUP BY clause is used to group rows that have the same values into summary rows. It is often used with aggregate functions like COUNT, MAX, SUM, AVG to group and summarize data.

Syntax

SELECT column1, aggregate_function(column2)
FROM table
GROUP BY column1;

GROUP BY goes after the FROM and WHERE clauses but before ORDER BY and HAVING.

Rules

• GROUP BY groups rows based on one or more columns specified.

• Aggregate functions like SUM() perform calculations on the grouped rows.

• Columns in the SELECT must either be in the GROUP BY clause or passed into an aggregate function.

Example

SELECT department, SUM(salary) 
FROM employees
GROUP BY department;

Groups rows by department and sums the salaries.

ORDER BY Clause

Overview

ORDER BY sorts the rows in the output result set based on one or more columns.

Syntax

SELECT columns
FROM table
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC];

Order directions are ascending (ASC) by default or descending (DESC).

Rules

• ORDER BY appears after FROM, WHERE, GROUP BY and HAVING clauses.

• Columns can be sorted in ascending or descending order.

• Columns in ORDER BY don't have to be selected columns.

Example

SELECT * FROM customers
ORDER BY state, birth_date DESC;

Orders rows by state alphabetically, then birth date descending.

So in summary, GROUP BY aggregates rows into groups and ORDER BY sorts the final output rows.