Why can't I use the "AND" operator in the same column of a table?-CodePudding

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 ProductNames 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.