How to add multiple where conditions in mysql?

Question

Written by
Date: 2015-01-11 10:25:20 00:00

Nội dung chính Show

SELECT FROM WHERE multiple conditions
Parameters or Arguments
Example - With Single condition
Example - Using AND condition
Example - Using OR condition
Example - Combining AND & OR conditions
Example - Joining Tables
Can you have multiple WHERE clauses in MySQL?
Can we use 2 WHERE clause in SQL?
How do you do multiple conditions in SQL?
Can you use && in MySQL?

SELECT FROM WHERE multiple conditions

Maybe one of the most used MySQL commands is SELECT, that is the way to stract the information from the database, but of course one does not need all the info inside a database, therefore one should limit the info coming out from the table, there is WHERE statement comes into play, with it one can limit the data to only the one that complies with certain condition. What if that is still too wide?. Then it can be used multiple conditions. Here some options:

Working with two conditions

Using AND with two or more conditions the query can be narrowed to meet your needs.

SELECT * FROM table WHERE column1 = 'var1' AND column2 = 'var2';

Only when the two conditions are met the row is stracted from the database's table. What if any of them should be met to get the data?

SELECT * FROM table WHERE column1 = 'var1' OR column2 = 'var2';

Using OR will tell MySQL to return data if one or both conditions are met.

Working with more than two conditions

If more than two conditions need to be met in order to show a result, you need to use parenthesis and nest the conditions according to your needs. This time it will be easier with examples.

Consider this table:

+------------+-----------+--------+-----+
| fname      | lname     | gender | age |
+------------+-----------+--------+-----+
| John       | Smith     | M      | 30  |
+------------+-----------+--------+-----+
| Jane       | Doe       | F      | 20  |
+------------+-----------+--------+-----+
| Richard    | Stallman  | M      | 70  |
+------------+-----------+--------+-----+
| John       | Doe       | M      | 20  |
+------------+-----------+--------+-----+

If you want to get all young male's names use this query.

SELECT * FROM table WHERE gender = M AND age >=  '18' AND age <= '50';

If you want to get all young people with last name Doe or Smith, use this query.

SELECT * FROM table WHERE age >= '18' AND age <= '50' AND (lname = 'Doe' OR lname = 'Smith');

As you can see we are using parenthesis to get a result from last names, because you want one or the other, then you use AND to get the age range and finally an AND to join the results from age range and the results from names.

This MySQL tutorial explains how to use the MySQL WHERE clause with syntax and examples.

Syntax

The syntax for the WHERE Clause in MySQL is:

WHERE conditions;

Parameters or Arguments

conditionsThe conditions that must be met for records to be selected.

Example - With Single condition

It is difficult to explain the syntax for the MySQL WHERE clause, so let's look at some examples.

SELECT *
FROM contacts
WHERE last_name = 'Johnson';

In this MySQL WHERE clause example, we've used the WHERE clause to filter our results from the contacts table. The SELECT statement above would return all rows from the contacts table where the last_name is Johnson. Because the * is used in the SELECT, all fields from the contacts table would appear in the result set.

Example - Using AND condition

SELECT *
FROM suppliers
WHERE state = 'Florida'
AND supplier_id > 1000;

This MySQL WHERE clause example uses the WHERE clause to define multiple conditions. In this case, this SELECT statement uses the AND Condition to return all suppliers that are located in the state of Florida and whose supplier_id is greater than 1000.

Example - Using OR condition

SELECT supplier_id
FROM suppliers
WHERE supplier_name = 'Apple'
OR supplier_name = 'Microsoft';

This MySQL WHERE clause example uses the WHERE clause to define multiple conditions, but instead of using the AND Condition, it uses the OR Condition. In this case, this SELECT statement would return all supplier_id values where the supplier_name is Apple or Microsoft.

Example - Combining AND & OR conditions

SELECT *
FROM suppliers
WHERE (state = 'Florida' AND supplier_name = 'IBM')
OR (supplier_id > 5000);

This MySQL WHERE clause example uses the WHERE clause to define multiple conditions, but it combines the AND Condition and the OR Condition. This example would return all suppliers that reside in the state of Florida and whose supplier_name is IBM as well as all suppliers whose supplier_id is greater than 5000.

The parentheses determine the order that the AND and OR conditions are evaluated. Just like you learned in the order of operations in Math class!

Example - Joining Tables

SELECT suppliers.suppler_name, orders.order_id
FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id
AND suppliers.state = 'California';

This MySQL WHERE clause example uses the WHERE clause to join multiple tables together in a single SELECT statement. This SELECT statement would return all supplier_name and order_id values where there is a matching record in the suppliers and orders tables based on supplier_id, and where the supplier's state is California.

Learn more about MySQL joins.

Can you have multiple WHERE clauses in MySQL?

MySQL allows you to specify multiple WHERE clauses. These clauses may be used in two ways: as AND clauses or as OR clauses. What is Operator? An operator is a special keyword used to join or change clauses within a WHERE clause.

Can we use 2 WHERE clause in SQL?

Example - Two Conditions in the WHERE Clause (AND Condition) You can use the AND condition in the WHERE clause to specify more than 1 condition that must be met for the record to be selected.

How do you do multiple conditions in SQL?

Syntax. SELECT column1, column2, columnN FROM table_name WHERE [condition1] AND [condition2]... AND [conditionN]; You can combine N number of conditions using the AND operator.

Can you use && in MySQL?

The && , operator is a nonstandard MySQL extension. As of MySQL 8.0. 17, this operator is deprecated; expect support for it to be removed in a future version of MySQL. Applications should be adjusted to use the standard SQL AND operator.

programming mysql MySQL BETWEEN Select multi where Where in SQL IN MySQL MySQL Check NULL