CREATE table t1 (pk int PRIMARY KEY);
CREATE table t2 (pk int PRIMARY KEY, col1 int);
INSERT into t1 VALUES (1), (2), (3);
INSERT into t2 VALUES (1, 100), (2, 200), (3, 300);
SELECT * from t1 WHERE EXISTS (SELECT * from t2 where t1.pk = t2.pk);
/*
returns error:
operand should have 1 columns, but has 2
we expect to see results per:
https://dev.mysql.com/doc/refman/8.0/en/exists-and-not-exists-subqueries.html
*/
SELECT * from t1 WHERE EXISTS (SELECT count(*) from t2 where t1.pk = t2.pk);
/*
returns correct results:
+----+
| pk |
+----+
| 1 |
| 2 |
| 3 |
+----+
*/
SELECT * from t1 WHERE EXISTS (SELECT count(*) from t2 where t1.pk = t2.pk AND t2.col1 = 200);
/*
returns 3 rows correctly since 1 row is always returned by subquery:
+----+
| pk |
+----+
| 1 |
| 2 |
| 3 |
+----+
*/
SELECT * from t1 WHERE EXISTS (SELECT pk from t2 where t1.pk = t2.pk AND t2.col1 = 200);
/*
EDIT: this is correct
only returns 1 row which is incorrect:
+----+
| pk |
+----+
| 2 |
+----+
*/
