2 calls to the same table in Sqlite (output different than expected)

Question

Given the following 2 tables:

CREATE TABLE IF NOT EXISTS sugestoes (
    id     INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    texto  VARCHAR(250),
    autor  VARCHAR(250),
    itens  VARCHAR(250),
    pontos INTEGER
);

---

CREATE TABLE IF NOT EXISTS vertices (
    id            INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    id_node_pai   INTEGER,
    id_node_filho INTEGER,
    FOREIGN KEY (id_node_pai)   REFERENCES sugestoes(id),
    FOREIGN KEY (id_node_filho) REFERENCES sugestoes(id)
);

---

---

I’m doing the following consultation:

SELECT DISTINCT s.autor
FROM sugestoes AS s
LEFT JOIN vertices AS v
WHERE s.id <> v.id_node_pai; 

In order to get the table results sugestoes not on the table vertices. When I use the sign = it does the correct listing, showing only the same results (author 2 and author 4 of the example), but when I switch to the sign of <>, the result I have is all the names of authors in the table.

I do not know if the flaw is in my logic or in the way I am preparing the consultation.

Answer 1

If your database is Mysql, try the following:

SELECT autor
FROM sugestoes

MINUS

SELECT s.autor
FROM sugestoes AS s
INNER JOIN vertices AS v ON v.id_node_pai = s.id