MySQL Select Statement

The SELECT statement is used to select or retrieve data from one or more tables. You can use this statement to retrieve all rows/records from a table in one go, or to retrieve only those rows/records that satisfy a certain criteria/condition.

SELECT * FROM table_name;
SELECT emp_id, emp_name, hire_date, salary FROM employees;

WHERE clause

Selecting Record Based on Condition

The WHERE clause is used to filter records. The WHERE clause is used with SELECT statement to extract only those records that fulfill specified conditions.

SELECT * FROM employees WHERE emp_id = 2;
SELECT * FROM employees WHERE salary > 7000;

SQL requires single quotes around text values (most database systems will also allow double quotes). However, numeric fields should not be enclosed in quotes.

SELECT * FROM employees WHERE name = 'Ankit';

WHERE clause - Operators

SQL supports a number of different operators that can be used in WHERE clause.

S.No. OperatorDescriptionExample
=EqualWHERE id = 2
>Greater thanWHERE age > 30
<Less thanWHERE age < 18
>=Greater than or equalWHERE rating >= 4
<=Less than or equalWHERE price <= 100
<>, !=, NOTNot equals toWHERE price <> 100
WHERE price != 100
WHERE not price = 100
LIKEPattern matchingWHERE name LIKE 'Dav'
INCheck whether a specified value matches any value in a list or subqueryWHERE country IN ('India', 'UK'),
WHERE dept_id IN (1, 3),
WHERE dept_id NOT IN (1, 3)
BETWEENCheck whether a specified value is within a range of valuesWHERE age BETWEEN 20 AND 50
BETWEEN doj('2006-01-01' AS DATE) AND doj('2016-12-31' AS DATE)
WHERE emp_name BETWEEN 'p' AND 'Z';
ANDTRUE only if both conditions are TRUEWHERE salary > 7000 AND dept_id = 5;
ORTRUE when either of the conditions is TRUE.WHERE salary > 7000 OR dept_id = 5;

The AND and OR operators are used to filter records based on more than one condition

WHERE clause - Examples

SELECT * FROM student WHERE course = 'Web Designing' AND (city = 'Delhi' OR city = 'Haryana');
SELECT * FROM student WHERE NOT course = 'Web Designing' AND NOT course = 'Web Development';