Getting an empty result with ‘AND’ operation and wrong result with ‘OR’ operation in SQL

I have two tables and I want to find out the customer_id and customer_name of all customers who bought product A and B both.

Customers table:

+-------------+---------------+
| customer_id | customer_name |
+-------------+---------------+
| 1           | Daniel        |
| 2           | Diana         |
| 3           | Elizabeth     |
| 4           | Jhon          |
+-------------+---------------+

Orders table:
+------------+--------------+---------------+
| order_id   | customer_id  | product_name  |
+------------+--------------+---------------+
| 10         |     1        |     A         |
| 20         |     1        |     B         |
| 30         |     1        |     D         |
| 40         |     1        |     C         |
| 50         |     2        |     A         |
| 60         |     3        |     A         |
| 70         |     3        |     B         |
| 80         |     3        |     D         |
| 90         |     4        |     C         |
+------------+--------------+---------------+

In this example only the customers with id 1 and 3 have bought both the product A and B.

To find that i wrote this code –

SELECT distinct c.customer_id,
        c.customer_name
from customers c inner join orders o
on c.customer_id = o.customer_id
where o.product_name = 'A' and o.product_name = 'B'

When I am doing this I am getting an empty result.

So tried to use OR –

SELECT distinct c.customer_id,
        c.customer_name
from customers c inner join orders o
on c.customer_id = o.customer_id
where o.product_name = 'A' or o.product_name = 'B'

output –

customer_name   customer_id
Daniel              1
Diana               2
Elizabeth           3

Based on OR it is working right but I am still not getting the result I am trying to find. Because customer with id 2 only bought A and not Product B. And Using AND bringing me an empty result. I always feel confused with AND and OR operations. can someone help?

Answer

If you want both use aggregation:

select c.customer_id, c.customer_name
from customers c inner join
     orders o
     on c.customer_id = o.customer_id
where o.product_name in ('A', 'B')
group by c.customer_id, c.customer_name
having count(distinct product_name) = 2;

Note: This assumes that the data could have multiple rows for a customer and product. If that is not possible, just use count(*) = 2 for performance reasons.