MySQL Server : Open Source Database : Where Clause

• MySQL Where Clause
• Downloads Examples

The WHERE Clause and Basic Operator Symbols

• The WHERE clause is used to retrieve specific rows from tables.
• The WHERE clause can contain one or more conditions that specify which rows should be returned.

Syntax:

SELECT column, column FROM table WHERE conditions; 

Note: The less than (<) and greater than (>) signs are used to compare numbers, dates, and strings. the non-numeric values (e.g, dates and strings) in the WHERE clause must be enclosed in single quotes.

Example:

/*Create a report showing the title 
and rating of all films where rating is PG*/

SELECT title, rating FROM film WHERE rating = 'PG'; 

/*Create a report showing the first and last name of all customers 
that do not belong to store id 2.*/

SELECT store_id, first_name, last_name 
FROM customer 
WHERE store_id <> 2; 

Example:

-- Checking for films that run more than 3 hours
SELECT title, rating, rental_rate
FROM film
WHERE length >= 180; 

Checking for NULL

• When a field in a row has no value, it is said to be NULL.
• This is not the same as having an empty string. Rather, it means that the field contains no value at all.

Example:

/*Create a report showing films where category is not defined.*/
SELECT title, rating, rental_rate 
FROM film_details 
WHERE category_name IS NULL; 

SELECT last_name, first_name FROM staff_tab 
WHERE picture IS NOT NULL; 

Note: When checking to see if a field is NULL, you cannot use the equals sign (=); rather, use the IS NULL expression.

WHERE and ORDER BY

When using WHERE and ORDER BY together, the WHERE clause must come before the ORDER BY clause.

Example:

/*Create a report showing the first and last name of all customers 
whose last names start with a letter in the last half of the alphabet. 
Sort by Last Name in descending order.*/

SELECT first_name, last_name FROM customer
WHERE last_name >= ‘N‘
ORDER BY last_name DESC; 

Using Aliases

The column names and the table name can be followed by an optional AS subclause that allows you to assign an alias to these names.

Example:

SELECT fl.title AS 'Film Title', fl.rating AS 'Film Rating‘
FROM filmtab AS fl
WHERE fl.film_id = 998; 

Selecting Distinct Records

The DISTINCT keyword is used to select distinct combinations of column values from a table.

Example:

/*Find all the distinct ratings assigned to existing films.*/
SELECT DISTINCT rating FROM film;

/*How many distinct customers have rented movies.*/
SELECT COUNT(DISTINCT customer_id) AS 'Customer Count‘
FROM rental;

SELECT COUNT(customer_id) FROM customer;