Using this simple table, if I want to see only the records with ProductName 'Chai' and 'Chang' why can't I use the 'AND' operator?
===============================================
ProductName // SupplierID // CategoryID
Chai 1 1
Chang 1 1
Aniseed Syrup 1 2
===============================================
SELECT * FROM table_name
WHERE ProductName = 'Chai' AND ProductName = 'Chang';
This would return no results although the query would be succesful. I can use the 'OR' operator and the records are returned ok, but why doesn't 'AND' work?
CodePudding user response:
By writing WHERE ProductName = 'Chai' AND ProductName = 'Chang'
with AND
you are requesting the rows which fulfill both the condition ProductName = 'Chai'
and the condition ProductName = 'Chang'
.
Of course a row cannot have simultaneously two different values for ProductName
so this query will never return anything.
To obtain all rows with one of those ProductName
s you need to write WHERE ProductName = 'Chai' OR ProductName = 'Chang'
(or WHERE ProductName IN ('Chai','Chang')
) to request the rows which fulfill at least one of the two conditions.
CodePudding user response:
To "AND" the result of 2 queries together, use UNION
:
SELECT * FROM table_name
WHERE ProductName = 'Chai'
UNION
SELECT * FROM table_name
WHERE ProductName = 'Chang'
In your particular case, this can be simplified to:
SELECT * FROM table_name
WHERE ProductName IN ('Chai', 'Chang')
but I suspect that's not what you're asking.