Description:
With the following database:
```
CREATE TABLE FRIENDSHIP (
USER1_ID INTEGER,
USER2_ID INTEGER,
PRIMARY KEY (USER1_ID, USER2_ID)
);
INSERT INTO FRIENDSHIP VALUES (1, 0);
CREATE TABLE LIKES (
USER_ID INTEGER,
PAGE_ID INTEGER,
PRIMARY KEY (USER_ID, PAGE_ID)
);
INSERT INTO LIKES VALUES (2, 0);
```
By executing the following query,
```
SELECT * FROM (SELECT B.USER_ID, B.PAGE_ID FROM FRIENDSHIP A LEFT JOIN LIKES B
ON (A.USER2_ID=B.USER_ID OR A.USER1_ID=B.USER_ID)
WHERE (A.USER1_ID=1 OR A.USER2_ID=1) ) TMP
WHERE PAGE_ID NOT IN ( SELECT DISTINCT(PAGE_ID) FROM LIKES WHERE USER_ID=1);
```
the output is:
+---------+---------+
| USER_ID | PAGE_ID |
+---------+---------+
| NULL | NULL |
+---------+---------+
However, if we nest this SQL query just in a SELECT * FROM:
```
SELECT * FROM (
SELECT * FROM (SELECT B.USER_ID, B.PAGE_ID FROM FRIENDSHIP A LEFT JOIN LIKES B
ON (A.USER2_ID=B.USER_ID OR A.USER1_ID=B.USER_ID)
WHERE (A.USER1_ID=1 OR A.USER2_ID=1) ) TMP
WHERE PAGE_ID NOT IN ( SELECT DISTINCT(PAGE_ID) FROM LIKES WHERE USER_ID=1)
) AS T;
```
The result becomes empty:
Empty set (0.00 sec)
I think the second query should produce the same output as the first query?
How to repeat:
Example database:
CREATE TABLE FRIENDSHIP (
USER1_ID INTEGER,
USER2_ID INTEGER,
PRIMARY KEY (USER1_ID, USER2_ID)
);
INSERT INTO FRIENDSHIP VALUES (1, 0);
CREATE TABLE LIKES (
USER_ID INTEGER,
PAGE_ID INTEGER,
PRIMARY KEY (USER_ID, PAGE_ID)
);
INSERT INTO LIKES VALUES (2, 0);
Q1:
SELECT * FROM (SELECT B.USER_ID, B.PAGE_ID FROM FRIENDSHIP A LEFT JOIN LIKES B
ON (A.USER2_ID=B.USER_ID OR A.USER1_ID=B.USER_ID)
WHERE (A.USER1_ID=1 OR A.USER2_ID=1) ) TMP
WHERE PAGE_ID NOT IN ( SELECT DISTINCT(PAGE_ID) FROM LIKES WHERE USER_ID=1);
Q2:
SELECT * FROM (
SELECT * FROM (SELECT B.USER_ID, B.PAGE_ID FROM FRIENDSHIP A LEFT JOIN LIKES B
ON (A.USER2_ID=B.USER_ID OR A.USER1_ID=B.USER_ID)
WHERE (A.USER1_ID=1 OR A.USER2_ID=1) ) TMP
WHERE PAGE_ID NOT IN ( SELECT DISTINCT(PAGE_ID) FROM LIKES WHERE USER_ID=1)
) AS T;
The output of Q1 is different from Q2