# 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**

```sql
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**

```sql
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**

```sql
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**

```sql
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**

```sql
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**

```sql
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**

```sql
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**

```sql
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.


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://qatesting.gitbook.io/qa/database-testing/sql/dml-commands/clauses/filter-clauses.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.