When collecting data from a single table or merging many tables, the SQL WHERE clause sets a condition, only a specific value from the table is returned if the stated condition is met. Use the WHERE clause to filter the records and retrieve only the ones you need.
The WHERE clause is utilised in the SELECT statement and in the UPDATE, DELETE, and other words that we will look at in the following chapters.
The SELECT statement with the WHERE clause has the following basic syntax. WHERE [condition] SELECT column1, column2, columnN FROM table name
You can use comparison or logical operators like >, =, LIKE, NOT, etc. to describe a condition. The following examples can help you understand this concept.
Consider the CUSTOMERS table having the following records −
The following code retrieves the ID, Name, and Salary fields from the CUSTOMERS database, where the salary is larger than 2000 dollars.
mysql> select cus_id,cus_name,salary from customers where salary>2000;
The following query, for example, would retrieve the ID, Name, and Salary fields for a client named Hardik from the CUSTOMERS table.
It's vital to remember that all strings should be enclosed in single quotes ("). Numeric values, on the other hand, should be presented without any quotation marks, like in the example above.
mysql> select cus_id,cus_name,salary from customers where cus_name='Radhe';
The SQL AND & OR operators are used to restrict data in a SQL statement by combining various conditions. The conjunctive operators are these two operators. These operators allow you to perform several comparisons in the same SQL statement using separate operators.
The AND operator allows many conditions to exist in the WHERE clause of a SQL query.
The AND operator with a WHERE clause has the following basic syntax:
WHERE [condition1] AND [condition2]...AND [conditionN]; SELECT column1, column2, columnN FROM table name
Using the AND operator, you can combine an unlimited number of conditions. All conditions separated by the AND must be TRUE for the SQL statement to take action, whether it's a transaction or a query.
Consider the following records in the CUSTOMERS table
The ID, Name, and Salary fields from the CUSTOMERS database are fetched in the following example, where the salary is larger than 2000 and the age is less than 25 years.
SQL> SELECT ID, NAME, AND SALARY FROM CUSTOMERS WITH SALARY > 2000 AND AGE 25 FROM CUSTOMERS; This would yield the following result: