Problem

Given two tables, A and B, write a SQL query to find all the pairs of records (a, b) such that:
- a is in table A
- b is in table B
- a and b have the same value for column C
For example, given the following two tables:
Table A
| id | value |
|----|-------|
| 1  | 3     |
| 2  | 4     |
| 3  | 5     |
Table B
| id | value |
|----|-------|
| 4  | 3     |
| 5  | 6     |
| 6  | 7     |
The query should return the following pairs of records:
| a  | b  |
|----|----|
| 1  | 4  |
| 2  | 3  |

Solution

SELECT A.id, B.id
FROM A
INNER JOIN B
ON A.value = B.value

/*

The solution above is optimal because it uses an inner join to find the records that have the same value for column C.

*/