Select rows that are not indicated by another row in the self-reference table

Question

I have a self-regulation table, and I have problems finding rows that don't have other rows pointing to it, or in other words , getting those that are not parents to any other, that is, of course, they don't have children.

This is my table with example data:

+----+------+--------+
| id | name | cat_id |
+----+------+--------+
|  1 | C1   |        |
|  2 | C2   |        |
|  3 | C3   |      1 |
|  4 | C4   |      2 |
|  5 | C5   |      2 |
|  6 | C6   |      5 |
+----+------+--------+

Here cat_idis the parent. This is a "view":

. ├── C1 | └── C3 └── C2 ├── C4 └── C5 └── C6

, , cat_id. "" , . , , NULL cat_id, , ""?

:

SELECT
    c1.id, c1.name, c1.cat_id
FROM
    cat c1
INNER JOIN
    cat c2
ON
    c1.id != c2.cat_id

, , . . SQLFiddle.

? ?

Answer 1

, , :

select a.*
from cat a left outer join
   cat b on a.id = b.cat_id
where b.cat_id is null

Answer 2

- not exists , cat_id:

select id from cat c1
where not exists (
    select 1 from cat c2 where c2.cat_id = c1.id
)

Answer 3

Try the following:

select * from cat
where id not in (
    select distinct cat_id 
    from cat
    where cat_id is not null)

Searching for rows that do not have other rows pointing to it means searching for rows that are 'id'not in any row in the column 'cat_id'.