Let's call this table customers
:
+----+-------------+
| id | name |
+----+-------------+
| 1 | roger |
| 2 | steve |
| 3 | harold |
| 4 | tom |
+----+-------------+
And this is table orders
:
+----------+-------------+---------+
| order_id | customer_id | total |
+----------+-------------+---------+
| 101 | 1 | 25 |
| 102 | 1 | 18 |
| 103 | 3 | 149 |
| 104 | 4 | 299 |
+----------+-------------+---------+
How can I select only the rows in table customers
only if it has a row in the orders
table (by ensuring customers.id
matches the orders.customer_id
(and also ignore the duplicates)?
One canonical way of phrasing this would be to use exists logic:
SELECT c.id, c.name
FROM customers c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.id
);
We could also use a join based approach:
SELECT DISTINCT c.id, c.name
FROM customers c
INNER JOIN orders o
ON o.customer_id = c.id;
But we need to use a distinct select here to remove possible duplicates.