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.
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;
Download Examples |
Recent Comments