Select rows from current table only if its column matches another table's column

Question

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)?

Answer 1

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.