Evaluation of multiples 'IN' Expressions in 'WHERE' clauses in mysql
SELECT * FROM TABLE_NAME WHERE b IN(5,7) AND c IN(4,4)
This query will return rows, where b
is either 5
or 7
, AND c
is 4
.
What do you mean by "evaluation in pairs?"
Update:
I'll add one more row to the sample:
+----------+----------+----------+
| PK | b | c |
+----------+----------+----------+
| 1 | 2 | 3 |
+----------+----------+----------+
| 2 | 5 | 4 |
+----------+----------+----------+
| 3 | 7 | 9 |
+----------+----------+----------+
| 4 | 7 | 4 |
+----------+----------+----------+
| 5 | 2 | 9 |
+----------+----------+----------+
If you want to match the whole sets, you can use this syntax:
SELECT *
FROM table_name
WHERE (b, c) IN ((2, 3), (7, 9))
This means: "return all rows where b
is 2
and c
is 3
at the same time, OR b
is 7
and с
is 9
at the same time."
In the example above, this query will return rows 1
and 3
But if you rewrite this query the other way around, like this:
SELECT *
FROM table_name
WHERE b IN (2, 7)
AND c IN (3, 9)
, this will mean "return all rows where b
is either 2
or 7
, AND c
is either 3
or 9
).
This will return rows 1
, 3
and 5
, since row 5
satisfies the condition for the second query but not for the first one.
Multiple OR Clauses in MySQL
....
WHERE id IN (3, 9, 100, ...)
MySQL query with multiple OR conditions and a single AND condition
Use parentheses
SELECT *
FROM `nam_order_items`
WHERE (item_status = 'Glass Bending - Andy'
OR item_status = 'Glass Bending - Bren'
OR item_status = 'Glass Bending - Gary'
OR item_status = 'Glass Bending - James'
OR item_status = 'Glass Bending - Oscar')
AND (`backing_cut` = 0 OR `backing_cut` = 1 OR `backing_bent` = 0 OR `backing_bent` = 1)
you could also use value in array as
SELECT *
FROM `nam_order_items`
WHERE `item_status` IN ('Glass Bending - Andy','Glass Bending - Bren','Glass Bending - Gary','Glass Bending - James','Glass Bending - Oscar')
AND (`backing_cut` = 0 OR `backing_cut` = 1 OR `backing_bent` = 0 OR `backing_bent` = 1)
you could then build your array using php and simply do something like:
$options = array('Glass Bending - Andy','Glass Bending - Bren','Glass Bending - Gary','Glass Bending - James','Glass Bending - Oscar');
$sql = "SELECT * FROM `nam_order_items` WHERE `item_status` IN ({implode(',', $options}) AND (`backing_cut` = 0 OR `backing_cut` = 1 OR `backing_bent` = 0 OR `backing_bent` = 1)";
Edited to add option for backing cut = 1 or 0
Select rows that matches multiple and/or conditions
You can join the 3 tables, group by product and set the conditions in the HAVING
clause:
SELECT p.id, p.name
FROM products p
INNER JOIN product_tags_link pt ON pt.product_id = p.id
INNER JOIN tags t ON pt.tag_id = t.id
GROUP BY p.id, p.name
HAVING SUM(t.tag = 'color' AND t.value IN ('green', 'red')) > 0
AND SUM(t.tag = 'pet' AND t.value IN ('dog')) > 0
See the demo.
Drawback of using multiple conditions in where clause
the Database server has multiple options to solve that query. It will choose the option the server "thinks" is faster.
The options I see are:
- Scan the whole table and filter out rows that don't satisfy the
where
clause - Seek a
height
range on an index onheight
column, then filter out rows usingage between A and B
predicate. - Seek an
age
range on an index onage
column, then filter out rows usingheight between X and Y
predicate - Seek both indexes, then perform an index intersection
The database server not always use an index that might be applicable, it considers some things before using it, such as:
- The index selectivity.
- The index coverage.
High selectivity indexes are more likely to be used.
Covering indexes are likely used.
How to correctly combine multiple WHERE statements and multiple ON clauses when emulating a FULL OUTER JOIN in MySQL
Your problem is in the WHERE
clauses. You are excluding the records where the 'RIGHT' table record is null.
SELECT *
FROM c LEFT JOIN a ON ...
WHERE c.year = 2015 /*remove AND a.year = 2015*/
UNION SELECT *
FROM a LEFT JOIN c ON ...
WHERE a.year = 2015 /*remove AND c.year = 2015*/
Related Topics
What Is the SQL for 'Next' and 'Previous' in a Table
Can the "In" Operator Use Like-Wildcards (%) in Oracle
Does Facebook Fql Contain the SQL Like Operator
Generating Rows Based on Column Value
Strip Out Non-Numeric Characters in Select
How to Deal with Single Quote in Word Vba SQL Query
Convert One Row into Multiple Rows with Fewer Columns
How to Delete Multiple Rows in SQL Where Id = (X to Y)
Selecting Specific Row Number in SQL
Meaning of "Select Tables Optimized Away" in MySQL Explain Plan
Oracle SQL - Sum and Group Data by Week
Stored Procedure Parameter Default Value - Is This a Constant or a Variable
Extract Phone Number from Noised String
Select Second Most Minimum Value in Oracle