MySQL Server : Open Source Database : Where Clause
|MySQL Where Clause|
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.
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.
/*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;
-- 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.
/*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.
/*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;
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.
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.
/*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;