MySQL

Chapter 7 - DQL (Data Query Language)

WHERE Clause

The WHERE clause in MySQL is a key component of the Data Query Language (DQL) used to filter records from a table based on specific conditions. It allows you to retrieve only the rows that meet certain criteria, making it an essential part of almost every SQL query. Whether you're working with a single table or joining multiple tables, the WHERE clause helps refine your query results.

Here’s an overview of the WHERE clause with examples and additional tips for new students:

Basic Syntax:
- The WHERE clause is used to filter records that meet a specific condition.
- Syntax:
```
SELECT * 
FROM employees 
WHERE department_id = 3;
```

Using Multiple Conditions:

You can use AND and OR to combine multiple conditions in a WHERE clause.
Example of AND:

SELECT * 
FROM employees 
WHERE department_id = 3 
AND salary > 50000;

Example of OR:

SELECT * 
FROM employees 
WHERE department_id = 3 
OR department_id = 4;

Filtering with Comparisons:
- You can use comparison operators such as =, >, <, >=, <=, <> to create various conditions.
- Example:
```
SELECT * 
FROM employees 
WHERE salary >= 60000;
```
Using LIKE for Pattern Matching:
- The LIKE operator allows you to filter rows based on a pattern. This is useful for string matching.
```
SELECT * 
FROM employees 
WHERE name LIKE 'John%';
```
- % is a wildcard that matches any sequence of characters.
Using IN to Match Multiple Values:
- The IN operator lets you filter records that match any value within a specified list.
```
SELECT * 
FROM employees 
WHERE department_id IN (1, 3, 5);
```
Using BETWEEN for Range Filtering:
- You can use BETWEEN to filter rows within a specified range.
```
SELECT * 
FROM employees 
WHERE salary BETWEEN 40000 AND 80000;
```
Null Values Filtering with IS NULL:
- Use IS NULL or IS NOT NULL to check for NULL values.
```
SELECT * 
FROM employees 
WHERE manager_id IS NULL;
```

These examples demonstrate how to use the WHERE clause effectively. The ability to filter data with precision makes your queries more powerful and helps ensure that you retrieve only the necessary records.

Tansy SQL Course | WHERE Clause | Chapter 7 | Lesson 2 - Video Thumbnail

Test code

Retrieve data using the equality operator (=) on a numeric column to identify clients who are married.

SELECT * 
FROM org_clients
WHERE married_flag = 0 
;

Retrieve data by utilizing the equality operator (=) on a character column to identify female clients.

SELECT * 
FROM org_clients
WHERE gender = 'F' 
;

Retrieve data by utilizing the greater than (>) comparison operator on a numeric column to identify clients with a credit limit greater than $1,000.

SELECT * 
FROM org_clients
WHERE credit_limit > 1000 
;

s Retrieve data by using the greater than (>) comparison operator on a numeric column to identify clients with a credit limit less than $1,000.

SELECT * 
FROM org_clients
WHERE credit_limit < 1000 
;

select datRetrieve data by using the greater than or equal to (>=) comparison operator on a numeric column to identify clients with a credit limit greater than or equal to $1,000.

SELECT * 
FROM org_clients
WHERE credit_limit >= 1000 
;

select Retrieve data by using the greater than or equal to (>=) comparison operator on a numeric column to identify clients with a credit limit less than or equal to $1,000.

SELECT * 
FROM org_clients
WHERE credit_limit <= 1000 
;

Retrieve data by using the not equal to (<>) comparison operator on a numeric column to identify clients who are married.

SELECT * 
FROM org_clients
WHERE married_flag <>  0 
;

Retrieve data by using the not equal to (<>) comparison operator on a character column to identify clients who are not males.

SELECT * 
FROM org_clients
WHERE gender <> 'M' 
;

Retrieve data using the logical operator AND to identify unmarried male clients.

FROM org_clients
WHERE married_flag =  0 
AND gender = 'M' 
;

Retrieve data using the logical operator OR to find orders that are in OPEN status or orders that have not been shipped.

SELECT * 
FROM act_order
WHERE order_status_id =  1 -- Open 
OR shipped_date IS NULL 
;

Retrieve data using the logical operator LIKE – Find clients whose last name starts with 'MA'.

SELECT * 
FROM org_client
WHERE last_name LIKE 'Ma%'
;

Retrieve data using the logical operator LIKE – Find clients whose last names contain the string 'MA' in any position, where 'M' and 'A' are together.

SELECT * 
FROM org_client
WHERE last_name LIKE '%ma%'
;

Retrieve data using the logical operator NOT LIKE to find clients whose last names do not start with 'MA'.

SELECT * 
FROM org_client
WHERE last_name NOT LIKE 'Ma%'

;

Retrieve data using the logical operator BETWEEN – Find clients whose credit limit is between 2000 and 5000, inclusive of values 2000 and 5000.

SELECT * 
FROM org_client
WHERE credit_limit BETWEEN 2000 AND 5000 
;

Retrieve data using the logical operator BETWEEN to list orders that were ordered between January 1st and December 31st.

SELECT * 
FROM org_client
WHERE order_date BETWEEN '2022-01-01' AND '2022-12-31' 
;

This query will return clients whose city is either 'Albany', 'Buffalo', or 'Niagara Falls'. The IN operator is used to specify multiple values for comparison.

SELECT * 
FROM org_client
WHERE city IN ('Albany', 'Buffalo', 'Niagara Falls')
;

Retrieve data using the logical operator NOT IN – Retrieve clients who do not live in 'Albany' or 'Rochester'.

SELECT * 
FROM org_client
WHERE city NOT IN ('Rochester', 'Albany')
;

Retrieve data using the logical operator IS NULL to list orders that are not yet shipped.

SELECT * 
FROM act_order
WHERE shipped_date IS NULL
;

This query will return orders where the shipment_status is not NULL, indicating that they have been shipped.

SELECT * 
FROM act_order
WHERE shipped_date IS NOT NULL
;

This query will return orders where the shipment_status is not NULL (indicating they have been shipped) and the order_date is within the last 30 days.

SELECT * 
FROM act_order
WHERE shipped_date IS NOT NULL
AND order_date > DATE_ADD(CURDATE(), INTERVAL -30 DAY) --mysql

-- Oracle, AND order_date > sysdate-30
-- Postgres,order_date > CURRENT_DATE - 30 
-- MS SQL Server, order_date >  GETDATE() - 30
;

Retrieve data using the logical operator EXISTS to fetch clients who have placed orders, excluding clients who do not have any orders.

SELECT *
FROM org_client
WHERE EXISTS (SELECT * FROM act_order WHERE org_client.client_id = act_order.client_id);

This query will return clients for whom there does not exist any order, ensuring that only clients without associated orders are included.

SELECT *
FROM org_client
WHERE NOT EXISTS (SELECT * FROM act_order WHERE org_client.client_id = act_order.client_id);

The ANY operator is typically used in combination with a comparison operator to compare a value to a set of values returned by a subquery. Here's an example to find products that were sold with a quantity greater than one using the ANY operator:

SELECT *
FROM prd_product
WHERE product_id = ANY  (SELECT product_id FROM act_order_detail WHERE quantity > 1);

Example 1:

Let's explore the procedure of retrieving information from a designated table using the SQL WHERE cluase with string data. Fetch records for clients identified as females.

Example 1 - Raw data from client table

Example 1 - Query

SELECT * FROM org_client WHERE gender = 'F';

Example 1 - Query data mapping

In the provided image, the green color signifies the data that has been selected or satisfies the conditions specified in our query. Data points in red indicate information that does not meet the criteria set by the query.

Example 1 - Query Output

Example 2:

Let's explore the procedure of extracting information from a designated table using WHERE clause with numeric data. Fetch orders with statuses different from 5.

Example 2 - Raw data from orders table

Example 2 - Query

SELECT * FROM act_order WHERE order_status_id != 5;

Example 2 - Query data mapping

In the provided image, the green color signifies the data that has been selected or satisfies the conditions specified in our query. Data points in red indicate information that does not meet the criteria set by the query.

Example 2 - Query Output

Comments(0 comments)

Comments Not Found